locked
List of users who can access the SQL Server instance RRS feed

  • Question

  • Hi

    How can I get a list of users access the SQL Server instance?

    Thanks and Kind Regards,

    Nuwa

    Tuesday, May 29, 2012 2:39 AM

Answers

  • One method:

    SELECT name, type_desc
    FROM sys.server_principals
    WHERE type_desc IN('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP');

    For Windows groups, you can execute xp_logininfo to obtain members:

    EXEC xp_logininfo 'MyDomain\MyGroup','members';


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Uri DimantMVP Tuesday, May 29, 2012 7:21 AM
    • Marked as answer by Iric Wen Wednesday, June 6, 2012 2:27 AM
    Tuesday, May 29, 2012 2:47 AM

All replies

  • One method:

    SELECT name, type_desc
    FROM sys.server_principals
    WHERE type_desc IN('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP');

    For Windows groups, you can execute xp_logininfo to obtain members:

    EXEC xp_logininfo 'MyDomain\MyGroup','members';


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Uri DimantMVP Tuesday, May 29, 2012 7:21 AM
    • Marked as answer by Iric Wen Wednesday, June 6, 2012 2:27 AM
    Tuesday, May 29, 2012 2:47 AM
  • Thanks for the reply

    I could extract the information which I needed.

    Tuesday, May 29, 2012 3:37 AM