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.

---

Fix orphaned users, if any.

SELECT Db_name()     DB,
       NAME,
       sid,
       'exec sp_change_users_login ''Auto_Fix'',''' + NAME + '''' cmd
FROM   sysusers u
WHERE  1 = 1
       AND issqlrole = 0
       AND sid IS NOT NULL
       AND sid != 0x00
       AND NOT EXISTS (SELECT * FROM   master..syslogins WHERE  sid = u.sid)
       AND EXISTS (SELECT * FROM   master..syslogins WHERE  NAME COLLATE database_default = u.NAME)

---

Extract db users

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

SELECT name
   ,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

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')

---

Steps:

1. Save the output from the above scripts.
2. Perform the database restore.
3. Run the scripts saved in the step 1 to restore back the database permissions.

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.