Generating scripts for database role membership in SQL Server 2005

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