Extract SQL Server Roles Script

Excellent way to extract Server roles for users on a SQL Server machine. Useful for Migrations.

SET NOCOUNT ON 

SELECT  'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context' 

-- Role Members 
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1) 
        + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1) 
        + QUOTENAME(usr2.name, '''') AS '--Role Memberships' 
FROM    sys.server_principals AS usr1 
        INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id 
        INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id 
        WHERE usr2.name not in ('sa','DR')
        and usr2.name not like '##%##' and usr2.name not like 'NT%' and usr2.name not like '%.service%' and usr2.name not like '%.agent%'
        and usr2.name not like '%~%'
ORDER BY rm.role_principal_id ASC 

-- Permissions 
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS 
        + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS 
        + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS 
        + ']' AS '--Server Level Permissions' 
FROM    sys.server_permissions AS server_permissions WITH ( NOLOCK ) 
        INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id 
WHERE   server_principals.type IN ( 'S', 'U', 'G' ) 
        and server_principals.name not in ('sa','DR')
        and server_principals.name not like '##%##' and server_principals.name not like 'NT%' and server_principals.name not like '%.service%' and server_principals.name not like '%.agent%'
        and server_principals.name not like '%~%'
ORDER BY server_principals.name, 
        server_permissions.state_desc, 
        server_permissions.permission_name 
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: