2

I have three application databases: A, B and C.

Users of A and B are all users in C; the users from A are in the public role with only SELECT permission, while the users from B are in a role with more permissions.

Due to some recent changes, all of the users on C need to be in the other role and not just public.

Since this will go out as a database patch and be executed automatically on 150+ databases, I would prefer it to be a simple, blanket script...i.e., a single statement that says "put all users in this role".

I know that I can use the script below to create exec statements and loop through the results and execute them, but I'm hoping for something a bit more streamlined.

I've searched around all over the place and can't find anything, but there's always hope for some undocumented system stored procedure that someone knows about.

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
WHERE roles.name = 'TheNewRole'
2
  • 1
    Knowing precious little about the security side of things, is it safe to assume you can't just add the users into a domain group and then assign permissions to the group?
    – billinkc
    Sep 16, 2011 at 21:11
  • Nope, no domain groups...mix of Windows and SQL Server authenticated users.
    – Wil
    Sep 16, 2011 at 21:21

2 Answers 2

2

Adam Machanic has a blog post on replacing xp_execresultset which in SQL 2000 did what you want: Replacing xp_execresultset in SQL Server 2005

1
  • Thank you, that might just do the trick because I can get it to work with our deployment system.
    – Wil
    Sep 19, 2011 at 21:44
0

I know this question is originally for sql server 2005, and I am using sql server 2019 just I thought about making it adaptable.

I have a script here that would script out all the users that belong to the db_tester role.

This is useful if you want to see all useres that belong to a role, and also if you want to apply the same to a different database (provided all the users are already there).

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 roles.name = 'db_Tester'

the result of this (having only one user in that role would be in my server):

enter image description here

Now if I wanted to add all of the users to this particular role then I would change the script to:

declare @the_role varchar(50) = 'db_test'; --it must exist in the database

  SELECT the_script='ALTER ROLE [' +  @the_role + '] ADD MEMBER [' + users.name + ']' 
FROM sys.database_principals users
where 1=1
  AND users.type in ('S', -- SQL_USER
                     'G', -- WINDOWS_GROUP
                     'U') -- WINDOWS_USER
 AND users.name not in ('dbo','sys','information_schema','guest')

this seems to be alright, and it has worked for me in a couple of simple small databases, but any I have not tested it extensively.

another script to list all members of a role

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.