SQL Script to add and Remove Database role members

/*==========================================================

Author: Suleman Mohammed
Description: Scripts out statements to add & drop Database role members
for each of the Database roles, ordered by Database role name and Member name
===========================================================*/

SELECT [Database Role] = r.name
, [Member] = m.name
, [Script to Add member to Role]=
‘Exec sp_addrolemember ”’+ m.name+”’, ”’+r.name+””
, [Script to Drop member to Role]=
‘Exec sp_droprolemember ”’+ m.name+”’, ”’+r.name+””
FROM   sys.database_role_members rm
JOIN   sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN   sys.database_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