Script to take database users and respective roles backup

It is very important to take a database users and the respective privileges backup while you perform a database restore, as once the database restore complete we must replace the same access rights before so that application activities never disturb. 

to achieve this requirement we can use below script to take the specific database users and the roles. 

1234567891011121314151617181920Use <'database name here'>
go
SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + 
QuoteName(dp.name, char(39)) + 
') CREATE USER ' + QuoteName(dp.name) + 
IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') + 
IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';' COLLATE DATABASE_DEFAULT 
FROM sys.database_principals dp 
LEFT JOIN sys.server_principals sp 
ON sp.sid = dp.sid 
WHERE dp.type in ('G','U','S') 
go
SELECT 'exec sp_addrolemember ' + '''' + (r3.name) + '''' + ',' + '''' + (r2.name) + '''' + ';' 
FROM sys.database_role_members r1 
inner join sys.database_principals r2 
on r1.member_principal_id = r2.principal_id 
inner join sys.database_principals r3 
on r1.role_principal_id = r3.principal_id 
GO



Test code given below:

Use <'database name here'>
go
SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + 
QuoteName(dp.name, char(39)) + 
') CREATE USER ' + QuoteName(dp.name) + 
IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') + 
IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';' COLLATE DATABASE_DEFAULT 
FROM sys.database_principals dp 
LEFT JOIN sys.server_principals sp 
ON sp.sid = dp.sid 
WHERE dp.type in ('G','U','S') 
go
SELECT 'exec sp_addrolemember ' + '''' + (r3.name) + '''' + ',' + '''' + (r2.name) + '''' + ';' 
FROM sys.database_role_members r1 
inner join sys.database_principals r2 
on r1.member_principal_id = r2.principal_id 
inner join sys.database_principals r3 
on r1.role_principal_id = r3.principal_id 
GO


Post a Comment

Post a Comment

Emoticon
:) :)) ;(( :-) =)) ;( ;-( :d :-d @-) :p :o :>) (o) [-( :-? (p) :-s (m) 8-) :-t :-b b-( :-# =p~ $-) (b) (f) x-) (k) (h) (c) cheer
Click to see the code!
To insert emoticon you must added at least one space before the code.

Author Name

Contact Form

Name

Email *

Message *

Powered by Blogger.