locked
How can I determine DBRole membership for another UserID RRS feed

  • Question

  • Is there a way (WITHOUT IMPERSONATING end user), to determine INDIRECT DB role membership, i.e. Named (not current) User ID is member of AD Role which is member of DB Role?

    Scenario: A service (running as a service account) passes end user account name to a (protected) stored proc or UDF.  Row level security is set up based on DBRole membership.  However, only AD App Roles are assigned as DBRole members.

    SELECT IS_ROLEMEMBER('MyRole', 'MyDomain\MyAppRole') works, but SELECT IS_ROLEMEMBER('MyRole', 'MyDomain\MyUserID') returns NULL, since the individual User ID is not a direct member (or separate DB Login), but the User ID is a member of the AD group and has the permissions assigned via the group.  I have granted VIEW DEFINITION on the roles to the service account.

    [Edit] I should add: End user is member of AD User Role which is member of AD App Role.

    The following illustrates the issue:

    EXECUTE AS USER = @MyUserID;
    SELECT IS_ROLEMEMBER(@MyDBRole, @MyUserID);
    SELECT IS_MEMBER(@MyADAppRole);
    SELECT IS_ROLEMEMBER(@MyDBRole, @MyADAppRole);
    SELECT HAS_PERMS_BY_NAME (@MyDBRole, 'ROLE', 'VIEW DEFINITION');
    REVERT;
    Results are NULL, 1, 1, 1 respectively.


    • Edited by TBQ Thursday, October 26, 2017 10:28 PM
    Thursday, October 26, 2017 9:41 PM

All replies

  • Hi TBQ,

    Please correct me if I’m wrong:

    >>Is there a way (WITHOUT IMPERSONATING end user), to determine INDIRECT DB role membership, i.e. Named (not current) User ID is member of AD Role which is member of DB Role?

    Not that I’m aware of. You best bet is to impersonate the target user then check the role membership directly as the code below:
    EXECUTE AS USER = @MyUserID;
    SELECT IS_ROLEMEMBER(@MyDBRole);
    REVERT;
    

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by TBQ Friday, October 27, 2017 1:56 PM
    • Unmarked as answer by TBQ Monday, October 30, 2017 8:22 PM
    Friday, October 27, 2017 5:18 AM
  • The UserID to be impersonated also has to be a login (principal) in order for the EXECUTE AS to work.  So that does not provide a solution either, since AD App Roles are used to define login permissions, rather than individual user permissions (who assigns individual user permissions to their databases anyway?).

    Thanks anyway.

    • Marked as answer by TBQ Tuesday, October 31, 2017 8:59 PM
    • Unmarked as answer by TBQ Tuesday, October 31, 2017 9:00 PM
    Monday, October 30, 2017 8:28 PM