Change the roles of multiple security accounts

Question

I have many security accounts on the sql database and i want to remove/add roles to them based on a simple string comparison.

1. Basically i want to list all accounts
2. Filter out accounts that DON'T start with "MyDomain\"
3. Remove role A.
4. Add role B.

What i found out by now is that i use sp_helprolemember to list all the accounts and sp_addrolemember and sp_droprolemember. My problem is that i dont know how to "get" the output from sp_helprolemember and work with it.

My first attemt at a soltuion based of feedback.

DROP TABLE [dbo].[XTemp]
create table XTemp(DbRole sysname,MemberName sysname,MemberSID varbinary(85) )

insert XTemp exec sp_helprolemember 
select * from XTemp

I made a permanent table to make it simpler to test and debug.

SELECT [DbRole]
      ,[MemberName]
      ,[MemberSID]
  FROM [ARTICLE].[dbo].[XTemp]
  WHERE MemberName like Domain\%'
    exec sp_addrolemember 'OldRole MemberName

Answer 1

Assuming that you're using SQL 2005 or later, and executing sp_helprolemember without parameters, this is the query that sp_helprolemember runs (extracted using sp_helptext):

  select DbRole = g.name, MemberName = u.name, MemberSID = u.sid  
   from sys.database_principals u, sys.database_principals g, sys.database_role_members m  
   where   g.principal_id = m.role_principal_id  
    and u.principal_id = m.member_principal_id  
   order by 1, 2  

This should enable you to collect the information you need into a temp table.

If you'd rather stick to documented behaviour, you can store the output of the SP into a temp table:

create table #t
(DbRole sysname,
MemberName sysname,
MemberSID varbinary(85) 
)

insert #t
exec sp_helprolemember 

select * from #t

EDIT

There are two ways to use this data to amend your system. One is using a cursor:

DECLARE @memberName sysname

DECLARE curMember CURSOR fast_forward FOR
SELECT  MemberName
FROM #t 
WHERE MemberName LIKE 'Domain\%' 

OPEN curMember

FETCH NEXT FROM curMember INTO @memberName

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC sp_addrolemember 'OldRole', @memberName
    FETCH NEXT FROM curMember INTO @memberName

END

CLOSE curMember
DEALLOCATE curMember

The other is using dynamic SQL:

DECLARE @sql NVARCHAR(MAX),

SELECT @sql = 'EXEC sp_addrolemember ''OldRole'', ''' + MemberName + ''''
FROM #t
WHERE MemberName LIKE 'Domain\%' 

EXEC sp_executesql @stmt = @sql

As you can see the dynamic SQL version is more compact but requires more effort to maintain.

Remember that after you execute either statement, the data you extracted from sp_helprolemember into a table is no longer up to date, and should probably be refreshed.

Answer 2

You can use Excel to generate SQL queries - I know it sounds lame but it is very simple and powerful. It is especially well-suited for tasks that have to be performed once or only from time to time.

1. Copy results from Management Studio to Excel.
2. Remove rows and columns than you don't need.
3. Use a formula in column B (e.g. ="EXEC sp_dropsrvrolemember '"&A1&"', 'sysadmin'") to generate queries for values stored in column A (the formula can of course reference more than one column with input data and generate really complicated queries).
4. Copy generated queries from Excel to Management Studio.