[SOLVED] Generating scripts for database role membership in SQL Server 2005

Generating scripts for database role membership in SQL Server 2005

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

Solution

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