List users that belong to a specific database role

  • 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) ?

  • 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!

  • 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.

  • 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!

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

  • 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?

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

  • 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?

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

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