locked
How can I see the name of the Active Directory Group that I am logging in as a member RRS feed

  • Question

  • Suppose my integrated windows login id is:  WH.gov\nextPresident

    And I belong to groups   WH.gov\westWingUsers and WH.gov\eastWingUsers

    When I login to some servers, I get different permissions than other, because they are all not are configured the same.

    How can I see (SELECT....) the AD group (my parent group) name that my login connects as.

    Thank you


    Travis McGee

    • Moved by Tom Phillips Thursday, May 28, 2020 3:01 PM Security question
    Tuesday, May 26, 2020 5:24 PM

All replies

  • SELECT * FROM sys.login_token will list all your tokens, which includes all AD groups you are member of.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, May 26, 2020 9:28 PM
  • Hi Travis McGee, 

    Xp_logininfo returns information about Windows users and Windows groups. For more information , please check xp_logininfo (Transact-SQL)

    Hope it will help you .

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 27, 2020 2:37 AM
  • Both of you are close, but not there yet.

    I am a member of both of these AD Groups
    WH.gov\westWingUsers   - it has access to WestWing databases and half of Oval Office databases
    WH.gov\eastWingUsers   - it has access to EastWing databases and the other half of Oval Office databases
    When I do authenticated login to the server as AD User: WH.gov\nextPresident, how can I find out that I am coming in as a member of the WH.gov\westWingUsers or WH.gov\eastWingUsers.
    Believe it or not based on the networking\dns, etc. depending on the client (and its subnet, etc.) sometimes I login as a member of the WH.gov\westWingUsers and then when I rds into another machine and login in to the same server with same AD User Id, then I login as a member of the WH.gov\eastWingUsers.

    Does it make sense?



    Travis McGee

    Wednesday, May 27, 2020 4:53 PM
  • Hi Travis,

    You can also try whoami /groups at the command prompt.

    Wednesday, May 27, 2020 5:21 PM
  • Your AD login is a member of both groups.  You don't "come in" as a member of a group. Your AD logins is always a member of both groups.

    Wednesday, May 27, 2020 5:54 PM
  • Ok... in that case.... one more try

    Will repeat the issue again..... please use your imagination.

    Client: myLaptop  in subnetA;  myWinServer  in subnetB

    Target:  mySqlServer   (it has both of AD Groups that I belong to but security settings are slightly different)

    a) myLaptop => mySqlServer

    b) myLaptop => via RDP => myWinServer => mySqlServer

    between a) and b) ..... my access credentials are different.


    Travis McGee

    Wednesday, May 27, 2020 7:10 PM
  • Actually, I may use this......whoami /groups

    There are about 40-45 rows of Groups and Alias's .... will examine them in Excel and see the difference.


    Travis McGee

    Wednesday, May 27, 2020 7:13 PM
  • Sorry that it is difficult to use my imagination to realize your requirement .

    Could you please explain more ? Thanks in advance.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 28, 2020 2:53 AM
  • Honestly, I don't really understand. BUt i have a bit of code that i either wrote, stole, hacked .... awhile ago that might be useful. 

    /*******************************************************************
    
    4 result sets
    1) current user account name and windows group (if used)
    2) uses xp_logininfo to get all members (AD group) of Windows Groups that are SQL Logins
    	++ Not 100%, think SQL has to have access to AD
    3) SQL Logins
    4)  Result Set #4 - convert SID to username, even if username is in an AD group - give the name of job owner even if owner is in an AD group
    
    *********************************************************************/
     
    	declare @GroupName varchar(1056) 
    	declare @MemberName nvarchar(1056)  
    	declare @Note varchar(500)
    	declare @system_user varchar(128) = (select system_user)
    
    	If object_ID(N'tempdb.dbo.#WindowsGroupMembers') is not NULL
    		drop table #WindowsGroupMembers
    		
    	create table #WindowsGroupMembers ([account name] varchar(256), [type] varchar(8), [privilege] varchar(8),
    		   [mapped login name] varchar(256),[permission path] varchar(256) )
    
    	-- Result Set #1 - SYstem User Account and Windows Groups
    
    	insert into #WindowsGroupMembers
    		EXEC xp_logininfo @system_user,'all';
    
    	select 'SystemUser' as "SystemUser", [account name], [permission path] as WindowsGroup, type, [privilege] from #WindowsGroupMembers order by [account name]
    
    	truncate table #WindowsGroupMembers
    
    	-- Result Set #2 - SYstem User Account and Windows Groups
    	  
    	/** get list of all members in server principals Windows_Groups that are sysadmins  **/  
    	declare WindowsGroupsCursor cursor for
    		select name FROM sys.syslogins WHERE sysadmin = 1 and isntgroup = 1
    
    	open WindowsGroupsCursor
    	fetch next from WindowsGroupsCursor into @GroupName	
    
    	while @@FETCH_STATUS = 0
    	begin
     		begin try 
    			insert into #WindowsGroupMembers
    				exec xp_logininfo @GroupName, 'members'
    		end try
    		begin catch
    		end catch
    		fetch next from WindowsGroupsCursor into @GroupName	
    	end
    
    	close WindowsGroupsCursor
    	deallocate WindowsGroupsCursor
    
    	-- windowsgroupmembers
    	select 'WindowsGroupMembers' as "Members", [account name], [permission path] as WindowsGroup, type, [privilege] from #WindowsGroupMembers order by [account name]
    
    	
    	-- Result Set #3 - SQL Logins
    
    	select 'SQLLogins' as "SQLLogins", sl.name as SQLLogin, sp.type_desc, sl.* 
    		FROM sys.syslogins sl left join sys.server_principals sp
    		on sl.name = sp.name order by SQLLogin
    
    
    	-- Result Set #4 - convert SID to username, even if username is in an AD group - give the name of job owner even if owner is in an AD group
    
    	SELECT  s.name ,
            SUSER_SNAME(s.owner_sid) AS owner
    	FROM    msdb..sysjobs s 
    	ORDER BY name

    Thursday, May 28, 2020 1:55 PM
  • Ok, in your new example, you actually have 2 DIFFERENT AD logins, with different groups assigned.

    Login1 is a member of Group X and Group Y

    Login2 is a member of Group Y and Group Z

    SQL Server has different security for Group X and Group Y.

    When you login, the "effective" rights for Login1 are Group X + Group Y, and Login2 are Group Y.

    SQL Server does not differentiate between what groups your login is associated with, only the combined "effective" rights.


    Thursday, May 28, 2020 3:01 PM