I have many security accounts on the sql database and i want to remove/add roles to them based on a simple string comparison.
- Basically i want to list all
- Filter out accounts that DON’T start
- Remove role A.
- 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
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
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
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.
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.
- Copy results from Management Studio to Excel.
- Remove rows and columns than you don’t need.
- 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).
- Copy generated queries from Excel to Management Studio.