INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Microsoft SQL Server: Setup and Administration FAQ

User Management

How to find out what roles a user has. by mrdenny
Posted: 31 Aug 04

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

Back to Microsoft SQL Server: Setup and Administration FAQ Index
Back to Microsoft SQL Server: Setup and Administration Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close