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.