SQL Server Performance

IsMember for nested groups

Discussion in 'General DBA Questions' started by Evansosteopath, Sep 27, 2004.

  1. Evansosteopath New Member

    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
  2. Twan New Member

    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
  3. satya Moderator

    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.
  4. Evansosteopath New Member

    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
  5. Adriaan New Member

    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.
  6. Twan New Member

    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

Share This Page