8/07/2012

Script User Mappings for all SQL server logins sp_addrolemember

Sometimes after you've copied all users from one server to another (sp_help_revlogin) you need to copy all mappings (sp_addrolemember) as well. Execute the script from below in order to Script User Mappings for all SQL server logins and generate sp_addrolemember for all users.
SELECT 'EXEC sp_addrolemember '''+rp.name+''', '''+mp.name+'''' FROM sys.database_role_members drm
JOIN sys.database_principals rp ON (drm.role_principal_id = rp.principal_id)
JOIN sys.database_principals mp ON (drm.member_principal_id = mp.principal_id)
WHERE mp.name NOT IN ('dbo')
GO

No comments:

Post a Comment