Generating scripts for database role membership in SQL Server 2005

Question

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)?

Answer 1

Information about a database's users and the roles they are assigned to are made available in system views sys.database_principals and sys.database_role_members. Review this data with these queries:

select * from sys.database_principals
select * from sys.database_role_members

I'll assume you have database users and roles configured in database A, and you want them copied over to database B. To create the users in the target database:

• Run the following query in database A
• Cut, paste, REVIEW, and run the resulting script in database B

.

SELECT 'CREATE USER [' + name + '] for login [' + name + ']'
 from sys.database_principals
 where Type = 'U'
  and name <> 'dbo'

To configure the new users in B with the same roles as they have in A:

• Run the following query in database A
• Cut, paste, REVIEW, and run the resulting script in database B

.

SELECT 'EXECUTE sp_addrolemember ''' + roles.name + ''', ''' + 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

Always review these scripts. There may be exceptions or special cases going on, and you just don't want to mess up security.

If the new database is on a different SQL Server instance, you will have to create the SQL logins first. If you have user-defined roles, you will need to recreate them first. (Roles and the permissions assigned to them are very open-ended, and I don't ever want to be in a situation where I'd need to do that!)