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'