Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'
share|improve this question
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
add comment

1 Answer

up vote 2 down vote accepted

Adam Machanic has a blog post on replacing xp_execresultset which in SQL 2000 did what you want http://sqlblog.com/blogs/adam_machanic/archive/2006/10/19/replacing-xp-execresultset-in-sql-server-2005.aspx

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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