SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to get user roles


How to get user roles

Author
Message
Rog Saber
Rog Saber
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 231
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?
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40056 Visits: 32653
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
@fracionero
@fracionero
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 361
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

Francisco Racionero
twitter: @fracionero
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (10K reputation)

Group: Moderators
Points: 10302 Visits: 1917
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
@‌kbriankelley
alexkedrov
alexkedrov
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 69
f.racionero (10/17/2007)
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


Very good...
sasken
sasken
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1986 Visits: 1933
alexkedrov (7/8/2010)
f.racionero (10/17/2007)
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


Very good...


I want to add this to a cursor and have it select the name of the database as well..Can we add the name of database to it?

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
vikingDBA
vikingDBA
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 929
I had to do this just recently. Here is what I used to get a list of users and all the roles they belong to. They are listed more than once if they belong to more than one role.


-- set database context first

select members.name AS UserName, RTRIM(LTRIM(roles.name)) AS RoleName
from sys.database_principals members
inner join sys.database_role_members drm
on members.principal_id = drm.member_principal_id
inner join sys.database_principals roles
on drm.role_principal_id = roles.principal_id
WHERE members.name <> 'dbo'
ORDER BY members.name
vikingDBA
vikingDBA
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 929
Oops.. As far as permissions, I put it in a SPROC and set the WITH EXECUTE AS OWNER clause to the CREATE PROCEDURE statement and made the owner dbo.

Then gave all database users that needed it the access to run the SPROC.
nishav2
nishav2
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 103
Hi,

I need to know the list of users who fall under a particular Role.
For example there is a role named :SQLDEVADMINS.
I want to know the list of users who fall under this role.
How can I query the database.

Can anyone help me on this.

Thanks
Nisha V Krishnan
nishav2
nishav2
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 103
Hi All,

I found a way to query the Active Directory service to query the list of groups and the users assigned to that domain groups.
You can get the list of sysadmins on the box by querying as shown below :With the result from the query i was able to short list the users who fall under a particular Group (Group which has sysadmin role).Hope this would help someone.

SELECT p.name AS [loginname] ,
p.type , p.type_desc ,
p.is_disabled,
CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
--WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
-- Logins that are not process logins
AND p.name NOT LIKE '##%' -- Logins that are sysadmins
AND s.sysadmin = 1

Thanks
Nisha V Krishnan
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search