Master of None

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.
The script uses the sys.database_role_members, sys.database_principals, sys.database_permissions, sys.sysobjects and sys.sysusers tables.

Some things to note:

• Script does not remove or reduce rights if the user already exists.
• Create user statements that test if users already exits.
• Add users to existing database roles.
• Scripts object level grants.

Use the following to script out the existing target database users prior to the restore.


SET NOCOUNT ON
GO
-----To find out user access to dB--------
/*
JCD 11-08-2011
Script rights in a database before overlay.
+ Set Results to Text
+ Does not create logins or add to Server level roles
*/

— CREATE USERS
PRINT ‘USE ‘+ DB_NAME() +’
GO’

PRINT ‘—————————————-‘
PRINT ‘– Create users if they do not exists — ‘
PRINT ‘—————————————-‘
GO

SELECT ‘IF NOT EXISTS(Select name from sys.database_principals WHERE name = ”’+p.name+”’)
BEGIN
CREATE USER ‘ + QUOTENAME(p.name) + ‘ FOR LOGIN ‘ + QUOTENAME(p.name) + ‘;
END
GO ‘
FROM sys.database_principals p
JOIN sys.sysusers as u
on p.name=u.name
WHERE p.[TYPE] <> ‘R’
and p.principal_id>=5
ORDER BY p.NAME ASC
GO

— ADD ROLE MEMBERS
PRINT ‘—————————————-‘
PRINT ‘– Add Users to DB Roles — ‘
PRINT ‘—————————————-‘
GO

SELECT ‘EXEC master..sp_addrolemember ”’+pp.name+”’, ”’+p.name+”’;
GO’

FROM sys.database_role_members roles
JOIN sys.database_principals p
ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp
ON roles.role_principal_id = pp.principal_id
WHERE pp.name != ‘db_executor’
–and p.type!=’s’
ORDER BY pp.NAME ASC
GO

— OBJECT LEVEL GRANTS
PRINT ‘—————————————-‘
PRINT ‘– Grant Object Level Rights — ‘
PRINT ‘—————————————-‘
GO

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+’;
GO’
from sys.database_permissions perm
join sys.database_principals p
on p.principal_id = perm.grantee_principal_id
join sys.sysobjects as o
on o.id=perm.major_id ;
GO

Tiny URL for this post:
 Copy