List all members of a database role

Now it’s time for something more useful 🙂 These pieces of T-SQL come in handy when auditing permissions, especially tracking extra db_owner members. There is also a stored procedure shipped by Microsoft named sp_HelpRoleMember but it returns additional server-level principal SID, which is not always required.

List all members of a database role in one database

select r.name as role_name, m.name as member_name from sys.database_role_members rm 
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals m on rm.member_principal_id = m.principal_id
-- where r.name = 'db_owner' and m.name != 'dbo' -- you may want to uncomment this line

List all members of a database role in all databases

To get results from all database you may try a slightly adapted version (remember about sp_MSforeachdb issues!):

create table ##RolesMembers
(
	[Database] sysname,
	RoleName sysname,
	MemberName sysname
)

exec dbo.sp_MSforeachdb 'insert into ##RolesMembers select ''[?]'', ''['' + r.name + '']'', ''['' + m.name + '']'' 
from [?].sys.database_role_members rm 
inner join [?].sys.database_principals r on rm.role_principal_id = r.principal_id
inner join [?].sys.database_principals m on rm.member_principal_id = m.principal_id
-- where r.name = ''db_owner'' and m.name != ''dbo'' -- you may want to uncomment this line';

select * from ##RolesMembers
order by [Database], [RoleName]

drop table ##RolesMembers

List all members with associated logins used sp_HelpRoleMember

create table ##RolesMembers
(
	RoleName sysname,
	MemberName sysname,
	MemberSID varbinary(max)
)

insert into ##RolesMembers exec sp_helprolemember 'db_owner'

select rm.RoleName, rm.MemberName, sp.name as LoginName from ##RolesMembers rm
inner join sys.server_principals sp on rm.MemberSID = sp.sid

drop table ##RolesMembers

Those are the methods I use for checking database role membership quickly. If you have some of your own and want to share, I would love to see it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s