Home / server / Questions / 199822
Accepted
Boppity Bop
Asked: 2010-11-10 03:55:54 +0800 CST

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

  • 772

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

sql-server sql-server-2008

2 Answers

  1. Best Answer

    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.

    • 6

  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

    • 1