SQL Server – How to Fix Users

loginssql serversql-server-2008sql-server-2008-r2sql-server-2012

I Create a user in my database with following command :

CREATE USER Test WITHOUT LOGIN

I created a login with the Test Name.

I want to link Test user with Test Login.

When I use following command :

EXEC sp_change_users_login 'Update_One', 'Test', 'Test'

SQL Server raised bellow error :

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'Test' is absent or invalid.

And when I use following command :

ALTER USER Test WITH LOGIN = Test

SQL Server Raised Bellow error :

Msg 33016, Level 16, State 1, Line 2
The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

How can I link a Test user with Test login.

My purpose of this link :
I want to restore a database from a server to another server and after complete restoring, map users with existing login on the server.

thanks in advance.

Best Answer

A user that was explicitly created with WITHOUT LOGIN cannot be mapped to a login after the fact.

A user that was created with a login and then lost its association, e.g. because the database was moved to another server, can be remapped by first creating a new login (any name) and then using the ALTER USER ... WITH LOGIN = ... command.

To demonstrate I am going to create a database tst and then create a login tstlogin and an associated user tstuser within tst:

CREATE DATABASE tst;
GO
CREATE LOGIN tstlogin WITH PASSWORD = 'passw8rd';
GO
USE tst;
GO
CREATE USER tstuser FOR LOGIN tstlogin;

To show correct association we can use the following query:

SELECT DP.sid,DP.name user_name, SP.name login_name
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP
ON DP.sid = SP.sid
WHERE DP.name = 'tstuser';

This results in 

SID                                        user_name      login_name
------------------------------------------ -------------- --------------
0xBF3D0CC874C72B469BEB4AA93845F59E         tstuser        tstlogin

Now we will drop the login tstlogin

DROP LOGIN tstlogin;

and recheck our user:

SELECT DP.sid,DP.name user_name, SP.name login_name
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP
ON DP.sid = SP.sid
WHERE DP.name = 'tstuser';

which does not show an associated login anymore:

SID                                        user_name      login_name
------------------------------------------ -------------- --------------
0xBF3D0CC874C72B469BEB4AA93845F59E         tstuser        NULL

Next step is to create a new login. (I chose a diffenert name for demonstration purposes, but it could be the same name too.)

CREATE LOGIN tstlogin2 WITH PASSWORD = 'passw8rd';

To associate that new login with our user we can use the ALTER USER statement:

ALTER USER tstuser WITH LOGIN = tstlogin2;

Checking the association once again:

SELECT DP.sid,DP.name user_name, SP.name login_name
FROM sys.database_principals AS DP
LEFT JOIN sys.server_principals AS SP
ON DP.sid = SP.sid
WHERE DP.name = 'tstuser';

shows that the ALTER statement was successful:

SID                                        user_name      login_name
------------------------------------------ -------------- --------------
0xBF3D0CC874C72B469BEB4AA93845F59E         tstuser        tstlogin2

If you actually need to change a "WITHOUT LOGIN" user to be a "with login" user, you will have to drop that user and recreate it. Make sure in that case to script out the permissions first, as dropping a user causes SQL Server to forget those.

Related Question