Wednesday, January 14, 2009

Scripting Server Permissions And Role Assignments

No doubt at some point you’ve had to move or copy a database from one server to another. Usually you’d use sp_help_revlogin to script out any server logins that you needed to preserve but it falls short on scripting the server permissions and role assignments associated with said logins. Here’s a script that I use to accomplish that task:

/* Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */

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

-- Role Members
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
QUOTENAME(, '''') + ', @loginame =' + SPACE(1)
QUOTENAME(, '''') 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
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 [' + 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' )
Post a Comment