3

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

2
  • 1
    was the db restored from somewhere else? Nov 9, 2010 at 12:01
  • actually it was.. but i am almost sure this particular login has to be added after that Nov 9, 2010 at 12:08

2 Answers 2

6

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.

2
  • @Bobb - good luck with that! I hope your DBA's are tame...
    – Guy
    Nov 9, 2010 at 12:38
  • 1
    Finally a post with the ALTER USER command ! :) Thank you! Jun 11, 2014 at 12:32
1

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

2
  • his question is about sql 2008 behaviour, that's from 2012 ;) Jul 10, 2013 at 12:34
  • The behaviour I've described works in SQL 2005. Jul 16, 2013 at 2:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .