All DBA’s will be tasked at time to refresh a copy of production to a development, reporting or promote as adatbase to a UAT or QA environment. Many times it is important to retain the existing users rights on the target instance.
\nThe script uses the sys.database_role_members, sys.database_principals, sys.database_permissions, sys.sysobjects and sys.sysusers tables.<\/p>\n
Some things to note:<\/p>\n
\nUse the following to script out the existing target database users prior to the restore.<\/p>\n
— CREATE USERS PRINT ‘—————————————-‘ SELECT ‘IF NOT EXISTS(Select name from sys.database_principals WHERE name = ”’+p.name+”’) — ADD ROLE MEMBERS SELECT ‘EXEC master..sp_addrolemember ”’+pp.name+”’, ”’+p.name+”’; FROM sys.database_role_members roles — OBJECT LEVEL GRANTS SELECT ‘GRANT ‘ +( perm.permission_name COLLATE SQL_Latin1_General_Pref_CP1_CI_AS)+’ ON ‘+ QUOTENAME(OBJECT_SCHEMA_NAME (perm.major_id))+’.’+QUOTENAME(OBJECT_NAME (perm.major_id))+’ to ‘ + p.name+’; All DBA’s will be tasked at time to refresh a copy of production to a development, reporting or promote as adatbase to a UAT or QA environment. Many times it is important to retain the existing users rights on the …<\/p>\n
\nSET NOCOUNT ON
\nGO
\n-----To find out user access to dB--------
\n\/*
\nJCD 11-08-2011
\nScript rights in a database before overlay.
\n+ Set Results to Text
\n+ Does not create logins or add to Server level roles
\n*\/<\/code><\/p>\n
\nPRINT ‘USE ‘+ DB_NAME() +’
\nGO’<\/p>\n
\nPRINT ‘– Create users if they do not exists — ‘
\nPRINT ‘—————————————-‘
\nGO<\/p>\n
\nBEGIN
\nCREATE USER ‘ + QUOTENAME(p.name) + ‘ FOR LOGIN ‘ + QUOTENAME(p.name) + ‘;
\nEND
\nGO ‘
\nFROM sys.database_principals p
\nJOIN sys.sysusers as u
\non p.name=u.name
\nWHERE p.[TYPE] <> ‘R’
\nand p.principal_id>=5
\nORDER BY p.NAME ASC
\nGO<\/p>\n
\nPRINT ‘—————————————-‘
\nPRINT ‘– Add Users to DB Roles — ‘
\nPRINT ‘—————————————-‘
\nGO<\/p>\n
\nGO’<\/p>\n
\nJOIN sys.database_principals p
\nON roles.member_principal_id = p.principal_id
\nJOIN sys.database_principals pp
\nON roles.role_principal_id = pp.principal_id
\nWHERE pp.name != ‘db_executor’
\n–and p.type!=’s’
\nORDER BY pp.NAME ASC
\nGO<\/p>\n
\nPRINT ‘—————————————-‘
\nPRINT ‘– Grant Object Level Rights — ‘
\nPRINT ‘—————————————-‘
\nGO<\/p>\n
\nGO’
\nfrom sys.database_permissions perm
\njoin sys.database_principals p
\non p.principal_id = perm.grantee_principal_id
\njoin sys.sysobjects as o
\non o.id=perm.major_id ;
\nGO<\/p>\n","protected":false},"excerpt":{"rendered":"