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 */
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
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')
ORDER BY server_principals.name ,
server_permissions.state_desc ,
server_permissions.permission_name;About Kendal
Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.
7 comments
Kendal Van Dyke shares his recipe for scripting server permissions and role assignments...
Log Buffer #131
Thanks Kendall - used this today.
Thank you Kendall! This is exactly what we needed!
Exactly what i needed, thanks for sharing
This is great! This was one of the missing links I was looking for in our migration. Thank You!
Generally some users can satisfy with the existing images but some users want to make some creativity or changes to an image. In order to fulfil their demands Python provides various programs. Let's see how Python used in imaging applications unindent does not match any outer indentation level python
This script is very helpful for capturing all the permissions associated with a login.
Post a Comment