5

Within my SQL server logs, I am showing a successful login to a database from an account (SERVER\loginname) that I cannot find in either Security > Logins OR Database > Security > Users. This login is being used by a program to execute a task (which it does successfully), so it's not a security compromise, but I cannot for the life of me figure out where it is and I need to find it to make an adjustment.

A couple more notes: I do remember adding this specific login a while back and there was something special about how I had to do it because it is not a domain account. Instead, it uses pass through authentication (same local account name on the app and SQL server with the same password). I cannot use it to login via SSMS. It is not a part of a group membership. It is not showing when I do a SELECT from the master syslogins table.

Does anyone have any ideas for my brain-fart?

4
  • 1
    Did you look in sys.server_principals which replaces sys.syslogins if you're on 2005+? I would triple check group membership if you still can't see it in there.
    – Jon Seigel
    May 1 '14 at 2:40
  • @JonSeigel sys.server_principals also isn't showing it. And I know it isn't a group membership because I don't have any groups that have ownership to that database. I'm completely dumbfounded right now. May 1 '14 at 14:57
  • To confirm, BUILTIN\Administrators and BUILTIN\Users are not logins on the instance, correct?
    – MattyZDBA
    May 1 '14 at 16:21
  • @MattyZDBA That is correct. These logins are a part of the BUILTIN\Administrators group, but it is not mapped to the database. May 1 '14 at 17:15
1

Can you try running the following:

EXEC xp_logininfo @acctname = 'SERVER\loginname', @option='all';

This should return the SQL login the Windows user is being authenticated as.

Reference here.

4
  • Well, we're getting somewhere. It is saying that it is a part of the BUILTIN\Administrators group. However, this group is NOT mapped to that database. I have double and triple checked at both the Security > Logins level as well as Database > Security > Users. How is that possible that it is still authenticating successfully without it being explicitly mapped? May 1 '14 at 17:15
  • 1
    By default, BUILTIN\Administrators is mapped to the sysadmin fixed server role and therefore has the same permissions as the SA login
    – MattyZDBA
    May 1 '14 at 17:43
  • @MattyZDBA The owner of the database is not sa, however. I am using a separate sysadmin credential for ownership. Or would that not matter? To further troubleshoot, I have also eliminated all users from the database except dbo, guest, INFORMATION_SCHEMA, and sys. I am suspecting it is under the dbo login. However, I attempted to use another local administrator account that is setup exactly like this one (credentials on both app and SQL for pass-through) to execute the task in the program, and I am getting Login unsuccesful. May 1 '14 at 19:40
  • Does BUILTIN\Administrators have a login on the server? And if so, is it a member of one of the fixed server roles? The fixed server roles will provide database-level access without having a user account in a database. May 6 '14 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.