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'
  • 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 '11 at 21:11
  • Nope, no domain groups...mix of Windows and SQL Server authenticated users.
    – Wil
    Sep 16 '11 at 21:21

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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