locked
Getting roles from SQL Server Database RRS feed

  • Question

  • Is there a command(s) that I can use to get what role(s) a user belongs to in an SQL Server database.  I use windows authentication to determine if the user is allowed to access the database but now I want to get his/her roles for a specific application and set menu items accordingly.
    Tuesday, October 11, 2011 9:41 PM

Answers

  • You can use this script to solve your requirement:
    How to find out what roles a user has.

    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

    Mike [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by JHab Thursday, October 13, 2011 1:45 PM
    Thursday, October 13, 2011 11:47 AM

All replies

  • Hi,

    Take a look at the following links

    http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/96c4bbe9-ed2c-47dd-af9a-e3a6f668ff65

    http://msdn.microsoft.com/en-us/library/aa172599(SQL.80).aspx

    Regards,
    A.Murugan


    If it solved your problem,Please click "Mark As Answer" on that post and "Mark as Helpful". Happy Programming!
    Wednesday, October 12, 2011 11:04 AM
  • You can use this script to solve your requirement:
    How to find out what roles a user has.

    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

    Mike [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by JHab Thursday, October 13, 2011 1:45 PM
    Thursday, October 13, 2011 11:47 AM
  • The scripts are nice and do give me an understanding but it does not solve my real problem. 

    I was hoping to fine a simple command that I could execute within a WinForm that would verify if the user is a member of a specific role.  The IS_Member seems to work but returns false if the user belongs to the sysadmin group even though he/she is a member of the specfic role tested.

    I guess I could write a stored proc and then call the proc from the WinForm.

    Thursday, October 13, 2011 1:45 PM
  • You're welcome!

    I also found this, maybe you can have a try:

    http://www.sqlservercentral.com/Forums/Topic560731-338-1.aspx#bm560907

    select is_member('UserRole'), user_name(), suser_sname()
    execute as user = 'Carl.Federl'
    select is_member('UserRole'), user_name(), suser_sname()
    revert
    go
    


    Mike [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, October 13, 2011 6:25 PM