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
,type_desc
,'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
,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
,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
+'] 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.
