SQL Script to add and Remove SQL server role members

/*=========================================================
Author: Suleman Mohammed

Description: Scripts out statements to add & drop SQL server role members
for each of the SQL server roles, ordered by Server role name and Member name
==========================================================*/

SELECT

[Server Role] = r.name
,[Member] = m.name
,[Script to Add member to Role]=
‘Exec sp_addsrvrolemember ”’+ m.name+”’, ”’+r.name+””
,[Script to Remove member to Role]=
‘Exec sp_dropsrvrolemember ”’+ m.name+”’, ”’+r.name+””

FROM   sys.server_role_members rm
JOIN   sys.server_principals r ON rm.role_principal_id = r.principal_id
JOIN   sys.server_principals m ON rm.member_principal_id = m.principal_id
ORDER  BY r.name , m.name

This entry was posted in SQL Server, T-SQL Scripts and tagged , . Bookmark the permalink.

Leave a Reply