asked
Members in Multiple Domain Groups
Hi, This might be a basic question to ask, But I am newbie to this SQL world So little excited about few concepts Scenario: If WIN\SVR is member of WIN\Group1 with SysAdmin Privilege & same WIN\SVR belongs to WIN\Group2 with dbcreator Privilege And both these groups are having access to Server A Q1: how to see the exact Login name thru which this user is connecting to Server A? if I use SUSER_Sname() function, it would display only WIN\SVR user info neither Group1 nor Group2 . Q2: What would be the privileges for this user? Is't SA or DBcreator ? Thanks for your time..!
For question 1, there's no easy way. You can see who is in each group through the `xp_logininfo` procedure, like so: exec xp_logininfo 'DOMAIN\GROUP', 'members' This will tell you who is in the group you specify. But accounts don't log in **as groups**, so you can't tell "under which group" the account logged in; that question doesn't quite make sense, as the answer is "all applicable groups." So it's not as though WINSVR logged in as WINGroup1 or WINGroup2; instead, it logged in as WINSVR **with the rights associated to WINGroup1 and WINGroup2**. If Bob is a member of both groups, Bob's rights will be the combination of the two groups, with the standard "deny overrides grant/revoke" policy. So to answer question 2, the user would have sysadmin, which encompasses dbcreator. If WinGroup1 had dbreader rights on a database and WinGroup2 had dbcreator on the database, then WINSVR would have dbreader and dbcreator. If WinGroup2 had dbcreator and denyreader, then WINSVR would have dbcreator and denyreader (overriding dbreader).
Privileges are cumulative so they would get the most access granted based on Group membership - so SA. Deny could trump that though for normal users. If they belong to the SysAdmin role though then there is almost no way to limit them.
Q2: Nothing can restrict SA so the login will have SA rights. Q1: WINSVR *is* the exact login name through which the user is connecting. You can use something like the following to list which groups contain your the user's login: DECLARE @user sysname = SUSER_SNAME() EXEC XP_LOGININFO @user, 'all'
11 People are following this question.
