SQL Server Performance Forum – Threads Archive

IsMember for nested groups

Hi The IsMember method does not verify membership in nested groups but is there a function that does or is enumeration the only option? Thanks
CE

Hi ya, IsMember works for me for a user who is in a global group which is in a local group… although not sure if it works global within global or local within local… I’m not aware of there being another SQL function that can be used… Twan

The second consideration is that a user’s Primary Group ID (Domain Users, by default) isn’t part of the User object’s memberOf attribute or the Group object’s Members collection. As such, you can’t use IsMember to check Primary Group membership. AD handles Primary Groups differently because they’re considered a special case. As mentioned by Twan you may need to get information from other sources as nothing available in SQL. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Maybe i’m using the wrong arguments. Below is what i am using: ismember("LocalGroupName") The members of the ‘LocalGroupName’ are the domain groups and then within these domain groups (global) there are all the users. Does the local group need to be on the same server as the SQL server instance? I am also having problems with user permissions which might be connected…I create a login using the ‘LocalGroupName’ group which then for a database i use this login as a user and assign them the permissions but this is not working well. When a user logs in they don’t get the permissions that are assigned to the local group. Any ideas? Thanks for your help
Caroline
CE

The IS_MEMBER function in T-SQL also returns true for database roles that are member of the given database role, where the login is a member of the "member role" but not of the "group role". If you’re looking for the current user’s exact permissions on an object, then use the PERMISSIONS() function.

Hi Caroline, I do this too or at least something very similar… I have:
– an AD local group called domainl-group
– a sql log in for domainl-group with permissions assigned
– an AD global group called domaing-group
– AD users who are members of domaing-group and I then use the IsMember function within some procs to ensure that only this user can run the proc (because I don’t want to get nasty permission denied errors on the web site) This seems to work ok for me… are you able to post your security script (changing user/domain/group names as appropriate) and how you use IsMember within your sql objects…? Cheers
Twan

]]>