Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium



Posted on 2009-12-21
Medium Priority
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
  • 2
LVL 57

Accepted Solution

Raja Jegan R earned 668 total points
ID: 26103628
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

MikeWalsh earned 1332 total points
ID: 26125560
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

MikeWalsh earned 1332 total points
ID: 26125578
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

580 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