Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to find out what roles a user has.

User Management

How to find out what roles a user has.

by  mrdenny  Posted    (Edited  )
This code will tell you what roles in each database a user has. This can be very handy for security audits. To use, simply change the set @UserName = 'username' line to the username to wish to check on.
Code: 
declare @RoleName varchar(50)
declare @UserName varchar(50)
declare @CMD varchar(1000)

set @UserName = 'username'

create Table #UserRoles
(DatabaseName varchar(50), 
Role varchar(50))

create table #RoleMember
(DBRole varchar(100),
MemberName varchar(100),
MemberSid varbinary(2048))


set @CMD = 'use ?

truncate table #RoleMember

insert into #RoleMember
exec sp_helprolemember 

insert into #UserRoles
(DatabaseName, Role)
select db_name(), dbRole
from #RoleMember
where MemberName = ''' + @UserName + ''''

exec sp_MSForEachDB @CMD

select * from #UserRoles

drop table #UserRoles
drop table #RoleMember

If you wish to do a full audit of all the login role assigments on your server this code will assist you greatly.
Code: 
declare @RoleName varchar(50)
declare @CMD varchar(1000)

create Table #UserRoles
(DatabaseName varchar(50), 
Role varchar(50))

create table #RoleMember
(DBRole varchar(100),
MemberName varchar(100),
MemberSid varbinary(2048))


set @CMD = 'use ?

truncate table #RoleMember

insert into #RoleMember
exec sp_helprolemember 

insert into #UserRoles
(DatabaseName, Role)
select db_name(), dbRole
from #RoleMember'

exec sp_MSForEachDB @CMD

select * from #UserRoles

drop table #UserRoles
drop table #RoleMember
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top