Posted on 2009-12-21
Last Modified: 2012-08-13
I'm current;ly doing year end auditing for sql2005 server, upon checking for few servers in details xp_logininfo returned me values which I doubt whether its correct or not. I found a below user has admin privilege, but then when I checked in OS group level, this user did not exist either, how sql2005 map this xp_logininfo to the OS level?

xaccount name     type     privilege mapped login name                                                                                                                permission path
---------------- -------- --------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
PlX001\samtry   user     admin      PlX001\samtry                                                                                                                   NULL
Question by:motioneye
    LVL 57

    Accepted Solution

    Hope your machine name is PlX001
    And check whether samtry is either part of your Builtin\Administrators group..
    If not, it should have been part of Builtin\users group.

    Kindly confirm.
    LVL 13

    Assisted Solution

    Try this query SELECT * FROM sys.server_principals do you see that account listed? XP_LOGININFO normally doesn't extract the logins mapped through a group unless you specify more detail in the request. IF you just run XP_LOGININFO you should see every login either explicitly granted or every group explicitly granted logon rights to your server.

    While that account may not exist in windows, it may exist in your SQL Server as a hold over from when that account did exist. Can you see if you see it in that above query or in security-->logins at the instance level inside of SQL?

    LVL 13

    Assisted Solution

    And if samtry was a part of your BUILTIN\Administrators group they would be mapped into your SQL Server as a Sysadmin as RRJegan indicates if your builtin\admin group is granted SA rights (the default in versions prior to 2008) in the instance but I don't believe they would show up with just a simple query of XP_LOGININFO.

    I just tried it on a test instance here. I am a member of local admin group and that group has SA rights. When I run xp_logininfo I see "BUILTIN\Administrators" listed but not myself (I am a member of that group). If I add my domain account to SQL explicitly I see both the local group and my account returned in XP_LOGININFO.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now