Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    8

    Question Unanswered: more than one group

    hello

    I have a problem, I want to find the groups of a user.

    The problem is that the gid contain only one id. Anybody has an idea???

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    /*
    SQL Server security is very complex
    1.Athentification mode Windows//Windows/SQL.
    2.Login at server level, it can belong to predefined server group.
    If login is imported from Windows,it can be in multiple login groups.
    Stored in master.dbo.syslogins (view on master.dbo.sysxlogins)
    with denormalized server group membership info.
    3.User at database level=mapped server login.
    Stored in dbo.sysusers ((uid <> gid and altuid=0) or [password] is not null)
    together with database roles ( uid = gid or (altuid=1 and [password] is null) ),
    in gid there is only one group. All membership info stored in dbo.sysmembers link table.
    4.Every user is member of public role.
    5.Role can be owned.
    6.Roles can be chained (40 levels tested). Autochecked for circular relationship.
    7.Application roles (with password, cannot have members) are similar to users.
    */

    --For first level membership:
    select su.uid,su.name,sm.groupuid,user_name(sm.groupuid),[LEVEL]=1
    from dbo.sysusers su
    left join dbo.sysmembers sm
    on su.uid=sm.memberuid
    where (su.uid <> su.gid and su.altuid=0) or su.[password] is not null

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •