Database restoration is a very generic task which DBAs encounter on a daily basis. Be it restoration of a DEV copy from a prod one or creaing new TEST/QA/UAT environment. Many time such requests require a database to be overwritten by a some backup. In such cases its extremely important to make sure that the database that is going to be overwritten can be restored back if required. It mainly consists of following steps :-
- Extract a list of users who have access on the databases. Once databases is deleted all the permissions (and users ) for the users contained in that databases are lost. If users are not scritpted before overwriting the databases with a new backup. It would be difficult to ensure same access on the newly restored database.
E.g. A very common situation is when a DEV copy of a Databases D1 from Server DS1 is to be restored with a new production copy P1 from server DP1. Dev databases D1 will have have users with elevated privileges whereas production database P1 will have least required privileges. Once P1 backup is restored onto D1 , the new databases will have users from P1 databases (with prod set of users and privileges). The users might be completely different from the DEV databases and would not be properly mapped with the corresponding logins or even orphans. (We will come to the orphan part later). In such cases the easiest way to extract the users as well as their permissions before restoring any database.
You can use third party tools like Embarcadero’s DBArtisan to extract such permissions. The following scripts extracts object level permission from a database. This will give CREATE USER script and object level permissions for a database. This script has to be run on the databases you want permissions from.
SET NOCOUNT ON
SELECT Scripts AS '--Scripts'
FROM
(
SELECT getdate() AS ScriptDateTime,'CREATE USER ['+DP.name+'] FOR LOGIN ['+SP.name+']'
+ CASE WHEN DP.type_desc != 'WINDOWS_GROUP'
THEN ' WITH DEFAULT_SCHEMA = ['+ISNULL(DP.default_schema_name,'dbo')+']'--+ CHAR(13)+CHAR(10)+'GO'
ELSE ''--+ CHAR(13)+CHAR(10)+'GO'
END AS Scripts
FROM SYS.DATABASE_PRINCIPALS DP,SYS.SERVER_PRINCIPALS SP
WHERE SP.SID = DP.SID
AND DP.name NOT IN ('DBO','GUEST','INFORMATION_SCHEMA','SYS','PUBLIC','DB_OWNER','DB_ACCESSADMIN','DB_SECURITYADMIN','DB_DDLADMIN',
'DB_BACKUPOPERATOR','DB_DATAREADER','DB_DATAWRITER','DB_DENYDATAREADER','DB_DENYDATAWRITER','DB_X')
UNION
--Extracting role membership
SELECT getdate() AS ScriptDateTime,'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''')
--+ CHAR(13)+CHAR(10)+'GO'
AS '--Role Memberships'
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.role_principal_id)+USER_NAME(rm.member_principal_id) != 'DB_OWNERDBO'
--ORDER BY rm.role_principal_id ASC
UNION
--Extracting object level permissions
SELECT getdate() AS ScriptDateTime,CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END
--+ CHAR(13)+CHAR(10)+'GO'
AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION
--Extracting database level permissions
SELECT getdate() AS ScriptDateTime,CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END
--+ CHAR(13)+CHAR(10)+'GO'
AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
AND (permission_name+USER_NAME(usr.principal_id) != 'CONNECTDBO')
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
) AS UserScripts
ORDER BY Scripts
In many environments, such scripts are scheduled via SSIS packages, batch files or jobs to run periodically and save output at a central location so that in case of any disaster getting users back is easy.
Note :- This script doesn’t map users or takes care of orphan users. My next post will cover detection of orphan users and mapping them.
Related posts:
