SQL 2008. I have user in a db which has no login on the server. How is it possible?

Question

I am talking about windows authentication.

I dont have access to the server adming rights but a dbadmin sent me screenshot where my user is not in the logins of the server. and also there is only one windows group called admin - databases which I am 100% sure my guy cannot be part of it.

BUT... his username is in users of my db...

How come user can appear in a db not having login on the server?

P.S. in the logs it prints: Login failed for user 'xxxx'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors

Answer 1

If the database has been restored or migrated all logins will have to be re-associated. This is called orfan login.

if there is a login with the same name you can use this script to fix it.

USE [db_name]
GO
exec sp_change_users_login "auto_fix", "username";

Just by recreating the login after you restore the database will not reassociate it to the user. You have to alter the user to be associated to a given login

You can use

ALTER USER [X]
WITH LOGIN [Y];

Since you can have a user with name A associated with login B.

Remember: Login is per server, user is per database.

Answer 2

It's also quite possible to create users without logins.

CREATE USER sithlord WITHOUT LOGIN

These login-less users can be useful for impersonation, where data access might be filtered depending on the user context (i.e. custom row-level security).

EXEC AS USER = 'sithlord'

SELECT * FROM dbo.someView

REVERT

Answer 3

There are a few different reasons and both of the above answers are possibilities.