SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Generate SQL script to extract user permissions from a SQL database

When you restore a database, it will also restore all permissions along with it. But what if you are restoring over an existing database and it has different sets of permissions and you would like to preserve those after the restore?

For example, due to security policy of your organization or client the users in test and production are different, have different names, permissions etc...

A while back I was faced with exactly that and I decided to write a set of scripts to extract user permissions before restoring over the database.

Extract db users

-- before running this script, you should fix orphaned users first

   ,'IF  NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + name + ''')
   Exec sp_grantdbaccess '''+suser_sname(sid)+''', '''+name +'''' [Command to Add DB User],
   sid, name

from sys.database_principals
where principal_id>4 and type in('S', 'U' , 'G')
AND suser_sname(sid) IS NOT NULL  -- this is just a check just in case there are orphaned users

Extract user roles

SELECT db_name() [DatabaseName]
   ,name [RoleName]
   ,type_desc [RoleType]
     ,'IF  NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + name + ''' and type = ''R'')
     Exec sp_addRole ''' + name +'''' [Command To Create Role]
from sys.database_principals where type in('R','A')
and name<>'public' and is_fixed_role<>1

Extract db role membership

select user_name(DRM.member_principal_id) [DatabaseUser]
  ,user_name(DRM.role_principal_id) [DatabaseRole]
  ,DP.type_desc as [UserType]
  ,'Exec sp_addrolemember '''+ user_name(DRM.role_principal_id)+ ''','
  + '''' + user_name(DRM.member_principal_id)+'''' [Command To Add Role Members]

from sys.database_role_members DRM
inner join sys.database_principals DP on DRM.member_principal_id=DP.principal_id
where DRM.member_principal_id>1
Order by DatabaseUser

Extract Object Permissions

select state_desc + ' ' + permission_name + ' ON [' + SCHEMA_NAME(SO.schema_id) + '].['+OBJECT_NAME(DP.major_id)
+'] TO [' + USER_NAME(DP.grantee_principal_id) + ']' [Command to add Special Permissions]

from sys.database_permissions DP
INNER JOIN sys.database_principals DPS
ON DP.grantee_principal_id=DPS.principal_id
Inner Join sys.objects SO ON SO.object_id=DP.major_id
where DPS.name not in ('public','Guest')

You may need some additional scripts for your requirements. If so, please let me know and I do my best to find an answer for you.

Mission: SQL Homeostasis

Drupal is a Senior SQL Server consultant with over 20 years of experience in engineering innovative SQL Server solutions for high growth organizations. A truly SQL Server specialist and IT generalist, Drupal is a certified MCDBA, Oracle OCP-DBA and IBM WebSphere Administrator. He is also ITIL V3 certified at the Foundation and Intermediate levels as well as PMP.


Leave a comment on the original post [sqlpal.blogspot.com, opens in a new window]

Loading comments...