How to get user roles

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » How to get user roles

How to get user roles

Rate Topic

Display Mode

Topic Options

Author

Message

Rog Saber

Posted Tuesday, October 16, 2007 8:07 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, September 17, 2009 8:35 AM
Points: 71, Visits: 71

When a user logs into my application, I would like to see which DB roles are assigned to that user. Does someone know the correct SQL to give me that information?

Thank you

Would this work?
SELECT users.name, groups.name
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
And, would a user need special permission to execute this SQL?

Post #411310

Grant Fritchey

Posted Tuesday, October 16, 2007 8:23 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:03 PM
Points: 6,773, Visits: 13,205

You can use IS_MEMBER() to determine the current users inclusion in a particular group. There are also a series of dynamic management views (DMV) that access security. Try sys.database_role_members and sys.database_principals to see the list of users and their association within a role.

Something like this, as a start:

SELECT p.NAME
,m.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

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled

For better & quicker help read:
How to Post Performance Problems

Post #411323

f.racionero

Posted Wednesday, October 17, 2007 3:56 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, October 22, 2009 10:13 AM
Points: 245, Visits: 125

Normally I use this script to get all user information for a db

select
[Login Type]=
case sp.type
when 'u' then 'WIN'
when 's' then 'SQL'
when 'g' then 'GRP'
end,
convert(char(45),sp.name) as srvLogin,
convert(char(45),sp2.name) as srvRole,
convert(char(25),dbp.name) as dbUser,
convert(char(25),dbp2.name) as dbRole
from
sys.server_principals as sp join
sys.database_principals as dbp on sp.sid=dbp.sid join
sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join
sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id

Post #411624

K. Brian Kelley

Posted Wednesday, October 17, 2007 7:57 PM

Keeper of the Duck

Group: Moderators
Last Login: Tuesday, November 03, 2009 12:13 PM
Points: 6,234, Visits: 1,314

In SQL Server, roles can be nested, so that needs to be taken into account. In SQL Server 2000 you'd have to do recursion using temp tables. In SQL Server 2005 you can use a CTE. One small note, I've UNION ALLed to include public because all database principals are a member of public but it's not shown in the sys.database_role_members DMV:

WITH CTE_Roles (role_principal_id)
AS
(
SELECT role_principal_id 
FROM sys.database_role_members
WHERE member_principal_id = USER_ID()
UNION ALL
SELECT drm.role_principal_id
FROM sys.database_role_members drm
  INNER JOIN CTE_Roles CR
    ON drm.member_principal_id = CR.role_principal_id
)
SELECT USER_NAME(role_principal_id) RoleName
FROM CTE_Roles
UNION ALL
SELECT 'public'
ORDER BY RoleName;

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Contributing Author of How to Cheat at Securing SQL Server 2005 and Pro SQL Server 2008 Administration
| Professional Site | Blog |

View Brian Kelley's LinkedIn profile | Twitter

Post #412002

« Prev Topic | Next Topic »

Permissions