Script to extract user permissions info from a database prior to migration

–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
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 in database
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 database 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 Individual 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’)