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.
By selecting SQL user without login within SSMS' create user dialog, this is the T-SQL that is generated (with no other options besides the user name supplied):
USE [AdventureWorks2012]
GO
CREATE USER [User1] WITHOUT LOGIN
GO
If you were to create a login and attempt to alter the user with the new login, like the following attempt:
create login TestLogin1
with password = 'p@$$w0rd';
go
use AdventureWorks2012;
go
alter user User1
with login = TestLogin1;
go
You will get the following error:
Msg 33016, Level 16, State 1, Line 8
The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.
Because of that, I don't think you can link up a login to this type of user without recreating it. It should be relatively painless, as you could just script out the user, but change the parameters from WITHOUT LOGIN
to whatever login you want it to be mapped to.
EDIT: As pointed out by Aaron below, ensure that you're taking note of permissions and role memberships before making any changes. You'll want to ensure that you can get back to the permissions state you currently are in, if that's the desired behavior.
Best Answer
I decided to experiment with creating the user with a random password, and everything worked.