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?

开发者_StackOverflow中文版Perhaps there is any other, better tool for generating database scripts from an existing database (preferably, but not necessarily, free)?

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

Here's one option from Idera: http://www.idera.com/Products/Free-Tools/SQL-permissions/ It generates logins and permissions and may help you accomplish what you are attempting.

For DB User Roles

SELECT 'CREATE ROLE [' + name + ']' 
  FROM sys.database_principals 
  where type='R' and is_fixed_role = 0 and name != 'public'

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