Generating scripts for database role membership in SQL Server 2005

user_163573 0
Viewed 37254

I have a database with a lot of users in it. Those users belong to different built-in roles in the DB (eg db_ddladmin).

I want to generate a script that creates those same users with the same role assignments to use in a different database. SQL Management Studio seems to only generate sp_addrolemember calls for user-defined roles, not the build-in ones. Is there any way to make it script all roles?

Perhaps there is any other, better tool for generating database scripts from an existing database (preferably, but not necessarily, free)?

role membership sql-server-2005 scripting
4 Answers

Adjusting the original answer to use the new ALTER ROLE syntax:

SELECT 'ALTER ROLE  [' + roles.name + '] ADD MEMBER [' + users.name + '];'
from sys.database_principals users
inner join sys.database_role_members link
on link.member_principal_id = users.principal_id
inner join sys.database_principals roles
on roles.principal_id = link.role_principal_id
where users.name = 'MyUser'

This is database specific - run it in the database you want to extract roles for

user_16901930
Related