Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
1  
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

2 Answers 2

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]
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.

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
1  
Finally a post with the ALTER USER command ! :) Thank you! –  Vincent Vancalbergh Jun 11 at 12:32

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

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

 
discard

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.