Is_Rolemember with AD group

• rcarrier

  SSC-Addicted

  Points: 495

  More actions

  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

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  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?

• rcarrier

  SSC-Addicted

  Points: 495

  More actions

  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

• rcarrier

  SSC-Addicted

  Points: 495

  More actions

  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:-)