SQL Server Stored proc to get all the role membership for all users on all databases

SQL Server Stored proc to get all the role membership for all users on all databases:

create PROCEDURE AuditRoleMemberships
AS
BEGIN
SET NOCOUNT ON;

create table #t ( UserName nvarchar(128), DbName nvarchar(128), RoleName nvarchar(128) )

declare @command varchar(1000)

set @command = ‘
if ”?” not in(”master”,”msdb”,”model”,”tempdb”)
BEGIN
use [?]
insert into #t ( UserName, DbName, RoleName )
select users.name, ”?”, roles.name
from sys.database_principals users
inner join sys.database_role_members link
on link.member_principal_id = users.principal_id
inner join sys.database_principals roles
on roles.principal_id = link.role_principal_id
inner join master.sys.server_principals login
on login.sid = users.sid
END’

EXEC sp_MSforeachdb @command

select * from #t order by UserName, DbName, RoleName

drop table #t
END

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