Post reply

Is_Rolemember with AD group

  • January 4, 2017 at 12:31 pm

    #325032

    We are using a Windows AD group to control access to login and to a certain DB

    The login works perfect, anyone in the group can use a trusted connection and login.

    The problem is we assigned a User Defined Role to the Group. When we check if a user has that role using is_rolemember proving only 1 parameter that specifices the Role, it returns 0

    If I add the second parameter and use the AD group name as the principle it works and returns 1.

    However, we dont want to control the name of the AD group

    users of our software can choose the AD group name . I just want to see of the user that logged in has that role, via a AD group

    Any help is appreciated

    Thanks

    Richard

  • January 4, 2017 at 1:24 pm

    #1920751

    rcarrier (1/4/2017)

    ...The problem is we assigned a User Defined Role to the Group. When we check if a user has that role using is_rolemember proving only 1 parameter that specifices the Role, it returns 0

    If I add the second parameter and use the AD group name as the principle it works and returns 1.

    However, we dont want to control the name of the AD group...

    I'm having trouble duplicating your problem. I tried an experiment on a development database:

    EXECUTE AS LOGIN = 'MYDOMAIN\joedeveloper'

    SELECT is_rolemember('Application Development')

    REVERT

    SELECT is_rolemember('Application Development')

    the first is_rolemeber returns 1, even though joedeveloper is not directly in the 'Application Development' role, his AD group MYDOMAIN\ITDEV is. The second is_rolemenber returns 0 after I revert back to my login since I'm not a member of that role. So you're saying when the user calls something that has is_rolemember in it, it's returning 0? Maybe the stored procedure or whatever the user is running is executing under a different security context?

  • January 4, 2017 at 3:16 pm

    #1920776

    I'm trying that exact test and I get an error message

    "Cannot execute as the server principal because the principal "MYDOMAIN\test11" does not exist, this type of principal cannot be impersonated, or you do not have permission."

    What permissions does did you grant the AD group ? the only user in my DB is the AD group and its not assigned to any server role or any other role except my user definned

    Thanks

  • January 4, 2017 at 8:20 pm

    #1920788

    I agree ,

    This is working now and looks like a user error on my part.

    With your validation, it helped me discover the bug

    Thanks

    Richard:-)

Viewing 4 posts - 1 through 3 (of 3 total)

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