locked
Created role and added users to that role but users not inheriting rights or permissions. RRS feed

  • Question

  • I created a role called Documenters in several databases. Then I created a login called TestDocumenter and mapped it to 3 different databases thereby creating 3 users (1 in each database). The role I created I gave db_datareaders and db_datawriters. I thought that would be the end of it.

    But these users (TestDocumenters) CANNOT view the tables, etcs. until I explicitly add them to the db_datareader role, etc., even though TestDocumenters was added to those roles and the users were added to TestDocumenters. What am I missing here?
    Wednesday, February 8, 2012 7:03 PM

Answers

  • Try this:

    1. Create role Documenters in database

    2. Open Documenters role and add login TestDocumenter as a Role Member

    3. Open db_datareaders role and add role Documenters as a Role Member

    4. Open db_datawriters role and add role Documenters as a Role Member

    • Marked as answer by Peja Tao Monday, February 20, 2012 1:31 AM
    Wednesday, February 8, 2012 9:09 PM
  • You can use this query to which users and roles that are members of which roles:

    SELECT m.name AS membername, r.name AS rolname
    FROM   sys.database_principals m
    JOIN   sys.database_role_members rm ON m.principal_id = rm.member_principal_id
    JOIN   sys.database_principals r ON r.principal_id = rm.role_principal_id
    ORDER BY m.name, r.name

    I would presume that you somehow missed to add the users to TestDocumenters, or TestDocumenters to the built-in roles.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Thursday, February 9, 2012 7:43 AM
    • Marked as answer by Peja Tao Monday, February 20, 2012 1:31 AM
    Wednesday, February 8, 2012 10:53 PM

All replies

  • Try this:

    1. Create role Documenters in database

    2. Open Documenters role and add login TestDocumenter as a Role Member

    3. Open db_datareaders role and add role Documenters as a Role Member

    4. Open db_datawriters role and add role Documenters as a Role Member

    • Marked as answer by Peja Tao Monday, February 20, 2012 1:31 AM
    Wednesday, February 8, 2012 9:09 PM
  • You can use this query to which users and roles that are members of which roles:

    SELECT m.name AS membername, r.name AS rolname
    FROM   sys.database_principals m
    JOIN   sys.database_role_members rm ON m.principal_id = rm.member_principal_id
    JOIN   sys.database_principals r ON r.principal_id = rm.role_principal_id
    ORDER BY m.name, r.name

    I would presume that you somehow missed to add the users to TestDocumenters, or TestDocumenters to the built-in roles.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Thursday, February 9, 2012 7:43 AM
    • Marked as answer by Peja Tao Monday, February 20, 2012 1:31 AM
    Wednesday, February 8, 2012 10:53 PM
  • Thank you both. I don't think I would have gotten it without both responses. The query is very helpful to visualize the before and after I got in Kevin's response. But I do understand better.
    Thursday, February 9, 2012 2:10 PM