List users that belong to a specific database role

Question

List users that belong to a specific database role

Senchi

Ten Centuries

Points: 1184
More actions
January 15, 2017 at 7:12 pm

#333993

I have created a role on my database called 'SU'.
So how can I now list all users that are assigned to this database role (just users without 'dbo' or similar) ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

each database has a system view named sys.database_role_members, which is just ID's associated ot roles; you have to join that against database_principals to get what you are after.
you'll need something fancier if you have roles within roles, as you need to take that into account as well.

SELECT [rolz].[name] AS [RoleName] , [memz].[name] AS [MemberName] FROM [sys].[database_role_members] [relz] INNER JOIN [sys].[database_principals] [rolz] ON [relz].[role_principal_id] = [rolz].[principal_id] INNER JOIN [sys].[database_principals] [memz] ON [relz].[member_principal_id] = [memz].[principal_id];

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Senchi Ten Centuries Points: 1184 More actions · Answer 2

This, unfortunately,this works only if I run it on the server. If I run it from my program then I only get one entry and that is only the first record found.

Lowell SSC Guru Points: 323518 More actions · Answer 3

Senchi - Sunday, January 15, 2017 8:24 PM
This, unfortunately,this works only if I run it on the server. If I run it from my program then I only get one entry and that is only the first record found.

Those views are filtered. If you are sysadmin, you can see everything. If you are a normal user, you only see yourself, basically.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Senchi Ten Centuries Points: 1184 More actions · Answer 4

Is there a way arround ?
I need ordinary users to see who belongs in their group.

pietlinden SSC Guru Points: 63501 More actions · Answer 5

Senchi - Sunday, January 15, 2017 8:58 PM
Is there a way arround ?
I need ordinary users to see who belongs in their group.

Create a stored procedure that returns the data you want, and then grant execution rights to the groups/users that need to execute it?

Senchi Ten Centuries Points: 1184 More actions · Answer 6

Thats what I did but it does not work.Its like Lowell said.

pietlinden SSC Guru Points: 63501 More actions · Answer 7

Lowell - Sunday, January 15, 2017 8:39 PM
Senchi - Sunday, January 15, 2017 8:24 PM
This, unfortunately,this works only if I run it on the server. If I run it from my program then I only get one entry and that is only the first record found.
Those views are filtered. If you are sysadmin, you can see everything. If you are a normal user, you only see yourself, basically.

Can you cheat and use EXECUTE AS in the stored procedure to run it in a different security context?