Database Refresh and User Permissions

How often do you refresh the databases from production to development?
How often do you miss to capture the users and their permission on the existing database before you restore?

Restoring or refreshing databases from one environment to another is a regular activity that a DBA would perform as part of their job. And as part of the refresh activity it is very important to make note of the users and their permissions before proceeding with the restoration of database. Also it is equally important to apply the correct permission after the restore.



Here are 2 scripts which helps in capturing the users and their permission before the restore and helps in applying the captured permissions after the restore.

This Script has to be run before the database refresh Activity.
This will capture the permission into 4 tables named tbl_DatabaseUsers, tbl_RolesCaptured, tbl_RolesMembersCaptured and tbl_SpecialPermissionsCaptured. These tables will be created in master database.

/* This Script has to be run before the database refresh Activity */

if exists(select * from master.sys.objects 
where name in('tbl_RolesCaptured','tbl_DatabaseUsers','tbl_RolesMembersCaptured','tbl_SpecialPermissionsCaptured'))
BEGIN
Declare @MSG nvarchar(max)
Set @MSG='The recording tables tbl_RolesCaptured, tbl_DatabaseUsers, tbl_RolesMembersCaptured, tbl_SpecialPermissionsCaptured are already present in master database.
Please check and delete them and re-run the script.'
Raiserror (@msg,16,1)
Print 'Terminating the Script Run.'
Print '
Run the below commands'
Print 'Drop Table master..tbl_DatabaseUsers'
Print 'Drop Table master..tbl_RolesCaptured'
Print 'Drop Table master..tbl_RolesMembersCaptured'
Print 'Drop Table master..tbl_SpecialPermissionsCaptured'
END

Use SansSQL; --Change to the database name that you are going to refresh
SET NOCOUNT ON
--Create User defined Roles
Print '/************* Recording of Database Roles Started at '+ convert(varchar(30),getdate()) + ' *************/'
Print '/************* Recording Table is present in master database with the name tbl_RolesCaptured *************/'
select db_name() [DatabaseName]
    ,name [RoleName]
    ,type_desc [RoleType]
    ,'Exec sp_addRole ''' + name +'''' [Command To Create Role] 
INTO master..tbl_RolesCaptured
from sys.database_principals where type in('R','A')
and name<>'public' and is_fixed_role<>1
Print '/************* Recording of Database Roles Completed at '+ convert(varchar(30),getdate()) + ' *************/'
Print ' 

' 

--Granting DB Access
Print '/************* Recording of Database Users Started at '+ convert(varchar(30),getdate()) + ' *************/'
Print '/************* Recording Table is present in master database with the name tbl_DatabaseUsers *************/'
select name
    ,type_desc
    ,'Exec sp_grantdbaccess '''+suser_sname(sid)+''', '''+name +'''' [Command to Add DB User]
INTO master..tbl_DatabaseUsers
from sys.database_principals
where principal_id>4 and type in('S', 'U' , 'G')
Print '/************* Recording of Database Users Completed at '+ convert(varchar(30),getdate()) + ' *************/'
Print ' 

' 

--Add Role Members
Print '/************* Recording of Database Roles Members Started at '+ convert(varchar(30),getdate()) + ' *************/'
Print '/************* Recording Table is present in master database with the name tbl_RolesMembersCaptured *************/'
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]
INTO master..tbl_RolesMembersCaptured
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
Print '/************* Recording of Database Roles Members Completed at '+ convert(varchar(30),getdate()) + ' *************/'
Print ' 

' 

-- Add Special Permissions
Print '/************* Recording of Special Permissions Started at '+ convert(varchar(30),getdate()) + ' *************/'
Print '/************* Recording Table is present in master database with the name tbl_SpecialPermissionsCaptured *************/'
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]
INTO master..tbl_SpecialPermissionsCaptured
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')
Print '/************* Recording of Special Permissions Completed at '+ convert(varchar(30),getdate()) + ' *************/'

SET NOCOUNT OFF

This Script has to be run after the database refresh Activity.
This will apply the permission which were captured in the previous script.

/* This Script has to be run after the database refresh Activity */

if exists(select * from master.sys.objects 
where name in('tbl_RevokeDBAccessCommands'))
BEGIN
Declare @MSG nvarchar(max)
Set @MSG='The tables tbl_RevokeDBAccessCommands is already present in master database.
Please check and delete them and re-run the script.'
Raiserror (@msg,16,1)
Print 'Terminating the Script Run.'
Print '
Run the below commands'
Print 'Drop Table master..tbl_RevokeDBAccessCommands'
END

Use SansSQL; --Change to the database name that you are going to refresh
SET NOCOUNT ON
Print '/************* Recording of Database Users to Revoke Started at '+ convert(varchar(30),getdate()) + ' *************/'
Print '/************* Recording Table is present in master database with the name tbl_RevokeDBAccessCommands *************/'
--Revoke Access Commands
select name 'DatabaseUser'
    ,type_desc 'UserType'
    ,'Exec sp_revokedbaccess '''+name+'''' [Revoke Commands] 
Into master..tbl_RevokeDBAccessCommands
from sys.database_principals
where type in ('S','U','G')
and principal_id>4
Print '/************* Recording of Database Users to Revoke Completed at '+ convert(varchar(30),getdate()) + ' *************/'
Print ' 

' 

Print '/************* Revoking access to Database Users Started at'+ convert(varchar(30),getdate()) + ' *************/'
--Revoking access to Database Users
while (select COUNT(*) from master..tbl_RevokeDBAccessCommands)<>0
Begin
declare @RevokeCmd nvarchar(500)
select @RevokeCmd=[Revoke Commands] from master..tbl_RevokeDBAccessCommands
Exec (@RevokeCmd)
delete from master..tbl_RevokeDBAccessCommands where [Revoke Commands]=@RevokeCmd
END
Print '/************* Recording of Database Users to Revoke Completed at '+ convert(varchar(30),getdate()) + ' *************/'
Print ' 

'

Print '/************* Adding of Database Users Started at'+ convert(varchar(30),getdate()) + ' *************/'
--Adding Database Users
while (select COUNT(*) from master..tbl_DatabaseUsers)<>0
Begin
declare @AddDBUser nvarchar(500)
select @AddDBUser=[Command to Add DB User] from master..tbl_DatabaseUsers
Exec (@AddDBUser)
delete from master..tbl_DatabaseUsers where [Command to Add DB User]=@AddDBUser
END
Print '/************* Adding of Database Users Completed at '+ convert(varchar(30),getdate()) + ' *************/'
Print ' 

'

Print '/************* Adding of Database Roles Started at'+ convert(varchar(30),getdate()) + ' *************/'
--Adding Database Roles
while (select COUNT(*) from master..tbl_RolesCaptured)<>0
Begin
declare @AddDBRole nvarchar(500)
select @AddDBRole=[Command To Create Role] from master..tbl_RolesCaptured
Exec (@AddDBRole)
delete from master..tbl_RolesCaptured where [Command To Create Role]=@AddDBRole
END
Print '/************* Adding of Database Roles Completed at '+ convert(varchar(30),getdate()) + ' *************/'
Print ' 

'

Print '/************* Adding of Database Role Members Started at'+ convert(varchar(30),getdate()) + ' *************/'
--Adding Database Role Members
while (select COUNT(*) from master..tbl_RolesMembersCaptured)<>0
Begin
declare @AddRoleMembers nvarchar(500)
select @AddRoleMembers=[Command To Add Role Members] from master..tbl_RolesMembersCaptured
Exec (@AddRoleMembers)
delete from master..tbl_RolesMembersCaptured where [Command To Add Role Members]=@AddRoleMembers
END
Print '/************* Adding of Database Role Members Completed at '+ convert(varchar(30),getdate()) + ' *************/'
Print ' 

'

Print '/************* Adding of Special Permissions Started at'+ convert(varchar(30),getdate()) + ' *************/'
--Adding Database Role Members
while (select COUNT(*) from master..tbl_SpecialPermissionsCaptured)<>0
Begin
declare @AddSpecialPermission nvarchar(500)
select @AddSpecialPermission=[Command to add Special Permissions] from master..tbl_SpecialPermissionsCaptured
Exec (@AddSpecialPermission)
delete from master..tbl_SpecialPermissionsCaptured where [Command to add Special Permissions]=@AddSpecialPermission
END
Print '/************* Adding of Special Permissions Completed at '+ convert(varchar(30),getdate()) + ' *************/'


SET NOCOUNT OFF