none
Get DataBaseRole & ServerRole of Current User & Current Login RRS feed

  • Question

  • Hi

    I want to get DatabaseRole & serverRole  (which ever is possible, If both possible then both) with SID

    of

    1) Current LogedIn User

    2) Current Login

    Like If I do Server Auth then It may be get Current User ROles of both category

    If I do WIndow Authentication then It may be user or it may be login, or group in type login

    Can you please help me for it?

    Monday, December 10, 2012 4:00 PM

All replies

  • This will get you the database roles for a given @SID

    SELECT p.NAME
    FROM sys.database_role_members rm
    JOIN sys.database_principals p
    	ON rm.role_principal_id = p.principal_id
    JOIN sys.database_principals m
    	ON rm.member_principal_id = m.principal_id
    where m.sid = @SID


    Chuck Pedretti | Magenic – North Region | magenic.com

    Monday, December 10, 2012 8:12 PM
  • This should get you the sys roles

    SELECT p.NAME
    FROM sys.server_role_members rm
    JOIN sys.server_principals p
    	ON rm.role_principal_id = p.principal_id
    JOIN sys.server_principals m
    	ON rm.member_principal_id = m.principal_id
    where m.sid = @SID


    Chuck Pedretti | Magenic – North Region | magenic.com

    Monday, December 10, 2012 8:13 PM
  • I want both the role with sid of  current user & login
    Tuesday, December 11, 2012 4:27 AM
  • here is the query to get current user and sid. use in combination with query given above.

    select * from sysusers where name = SYSTEM_USER

    Tuesday, December 11, 2012 4:59 AM
  • But If there is a group added in logins not created user 

    Now we do login by by the user added to that login.(eg group name in Logins :Ad\Members & User Inside group is Vipul, Here not seperately user created for database)

    In that case how to get database & server role of current Login?

    Tuesday, December 11, 2012 8:47 AM