Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
was the db restored from somewhere else? – Nick Kavadias Nov 9 '10 at 12:01
actually it was.. but i am almost sure this particular login has to be added after that – Boppity Bop Nov 9 '10 at 12:08
up vote 5 down vote accepted

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

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

USE [db_name]
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


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

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

share|improve this answer
thank you. will tell the dbas to do that properly! :) – Boppity Bop Nov 9 '10 at 12:25
@Bobb - good luck with that! I hope your DBA's are tame... – Guy Nov 9 '10 at 12:38
Finally a post with the ALTER USER command ! :) Thank you! – Vincent Vancalbergh Jun 11 '14 at 12:32

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


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


share|improve this answer
his question is about sql 2008 behaviour, that's from 2012 ;) – Gabriel Guimarães Jul 10 '13 at 12:34
The behaviour I've described works in SQL 2005. – Scott Herbert Jul 16 '13 at 2:09

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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