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

Hypotaxis

This should be scriptable via use of the system tables/views. If no one else posts, I'll work something up later on. – Mendoza

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

Mendoza

Just what I was looking for! :) – Hypotaxis

perfect, a script to create a script.. i had tried scripting logins with Generate Script... but ran into permission issues, no issues now, thanks. – Longobard

Just a note, if your database has a case sensitive collation, it should be sp_addrolemember. I edited the answer above to reflect that. – Fitted

Answer 2

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.

Kovacs

Answer 3

For DB User Roles

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

Fawkes

When writing answers, try formatting them. Stackoverflow uses markdown. – Militarist

Answer 4

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

Mathison