To copy all users and their permissions for a database
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