SQL Server 2005 Easy way to add all users to a database role

Question

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'

score 2 · Accepted Answer · edited Nov 12 '18 at 12:40

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

edited Nov 12 '18 at 12:40

Aaron Bertrand

181,950
28
405
624

answered Sep 19 '11 at 18:32

MartinC

1,359
11
14

Marcello Miorelli · Answer 2 · 2023-06-16T12:55:38.557

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

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

SQL Server 2005 Easy way to add all users to a database role

2 Answers2