locked
My login not in list of Logins RRS feed

  • Question

  • Using SSMS, when I expand the Logins folder I do not see my user and I don't know which Active Directory group I am part of. I am logged in using Windows Authentication so I'm sure I gained access because my login is part of an AD group which has been granted access, but there are numerous AD groups in the logins list. How can I know which group I gained access through? Is there a T-SQL system view or query I can run that will tell me which login I'm currently mapped to?

    Database version: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

    Tuesday, January 4, 2011 4:31 PM

Answers

  • Hi MSG66,

    Type the below command and see if this works

    XP_LOGININFO 'domain\your_user_name','all'

    This command should return the domain groups that you can use to access the specific SQL server in question.

    You can also get the same level of information from xp_logininfo 'domain\your_user_name' ( without the ALL parameter).
    Consider a case where your user account is in more than 1 group that is added as a login in SQL server ,
    User account - domain\A
    Domain group - domain\gp1 and domain\gp2

    Executing the stored proc with all parameter gives you both the groups in the column "permission path" and without the all parameter will give you
    the group that you are currently authenticated which is done through the group that has got higher level of permission in SQL server.

    Note:
    When both the group has got equal level of permission in the sql server (say sysadmin) the stored proc xp_logininfo without second parameter all will return the only group GP1 based on alphabetical order

    Permissions to run XP_LOGININFO:
    Requires membership in the sysadmin fixed server role or membership in the public fixed database role in the master database with EXECUTE permission granted.


    Thanks, Leks
    • Proposed as answer by Lekss Wednesday, January 5, 2011 4:51 PM
    • Marked as answer by MGS66 Wednesday, January 5, 2011 6:09 PM
    Wednesday, January 5, 2011 3:28 AM

All replies

  • You can check in Active Directory User and Computers (dsa.msc) to which groups your domain login belongs to. Search for those groups that you belong to in SQL Server


    Pradeep Adiga
    Blog: sqldbadiaries.com

    Recent posts on my blog
    Tuesday, January 4, 2011 7:39 PM
  • Though this not a direct method as far as i know this is the closest method for your requirement if you wanted to see these info from SQL server.

     

    EXEC XP_LOGININFO 'AD\group_name','members'

    You would have to do this for all the groups and identify under which AD group your domain account was authenticated


    Thanks, Leks
    Tuesday, January 4, 2011 9:05 PM
  • I am not an Active Directory administrator so I do not have Active Directory dsa.msc tools.
    Tuesday, January 4, 2011 10:18 PM
  • try using: select system_user
    Vishal Gajjar MCITP.DBAdministrator MCITP.DBDeveloper MCITP.BIDeveloper
    Wednesday, January 5, 2011 1:53 AM
  • Hi MSG66,

    Type the below command and see if this works

    XP_LOGININFO 'domain\your_user_name','all'

    This command should return the domain groups that you can use to access the specific SQL server in question.

    You can also get the same level of information from xp_logininfo 'domain\your_user_name' ( without the ALL parameter).
    Consider a case where your user account is in more than 1 group that is added as a login in SQL server ,
    User account - domain\A
    Domain group - domain\gp1 and domain\gp2

    Executing the stored proc with all parameter gives you both the groups in the column "permission path" and without the all parameter will give you
    the group that you are currently authenticated which is done through the group that has got higher level of permission in SQL server.

    Note:
    When both the group has got equal level of permission in the sql server (say sysadmin) the stored proc xp_logininfo without second parameter all will return the only group GP1 based on alphabetical order

    Permissions to run XP_LOGININFO:
    Requires membership in the sysadmin fixed server role or membership in the public fixed database role in the master database with EXECUTE permission granted.


    Thanks, Leks
    • Proposed as answer by Lekss Wednesday, January 5, 2011 4:51 PM
    • Marked as answer by MGS66 Wednesday, January 5, 2011 6:09 PM
    Wednesday, January 5, 2011 3:28 AM
  • I am not an Active Directory administrator so I do not have Active Directory dsa.msc tools.
    You need not be an AD admin to query the Active Directory. Do you get any error when you open Run --> dsa,msc ?



    Pradeep Adiga
    Blog: sqldbadiaries.com

    Recent posts on my blog
    Wednesday, January 5, 2011 3:37 AM
  • Here is one way to know which Active Directory Groups that are also SQL Server logins have you as a member.  Of course, this assumes enough rights to see the server_principals:

    SELECT name FROM sys.server_principals
    WHERE IS_MEMBER(name) = 1
    ORDER BY name

    This will not tell you about all AD groups, only those with access to the SQL Server.

    Also, if you are a member of several groups it will not tell you which one gave you access, since your rights are the aggregate of everything granted to you.

    RLF

    Wednesday, January 5, 2011 2:33 PM
  • Also, following up on xp_logininfo, you can do the following to find membership of the current login.  (Note that 'ALL' gives you all the paths through which you access the server.)

    CREATE TABLE #ADMembership
    (account_name SYSNAME,
     type NVARCHAR(10),
     privilege NVARCHAR(10),
     mapped_login_name SYSNAME,
     permission_path SYSNAME);

    DECLARE @login SYSNAME;
    SET @login = SUSER_SNAME();

    INSERT INTO #ADMembership
    EXEC XP_LOGININFO @login,'ALL';

    -- Do something with the data that is
    -- more interesting than this select.
    SELECT * FROM #ADMembership;

    DROP TABLE #AdMembership

    All the best,
    RLF

     

    • Proposed as answer by Pradeep Adiga Wednesday, January 5, 2011 4:14 PM
    Wednesday, January 5, 2011 4:01 PM
  • try using: select system_user
    Vishal Gajjar MCITP.DBAdministrator MCITP.DBDeveloper MCITP.BIDeveloper

    Vishal -

    This only returns my Domain\Network_Username. My Domain\Network_Username is not visible in the Logins list. So I have to assume that I am granted access because I am part of one of the Domain\groupnames that I DO see in the Logins list.

    Wednesday, January 5, 2011 6:02 PM