Assign a login to a user created without login (SQL Server)

Question

I have got a user in my database that hasn't got an associated login. It seems to have been created without login.

Whenever I attempt to connect to the database with this user I get the following error:

Msg 916, Level 14, State 1, Line 1
The server principal "UserName" is not able to access the database 
"DatabaseName" under the current security context.

I'd like to specify a login for this user so that I can actually use it to access the database. I've tried the following script to associate a login with the user.

USE [DatabaseName]
ALTER USER [UserName]
WITH LOGIN = [UserName]

But this gives me the following 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.

Is there any way I can assign a login to this user? I'd like to not have to start from scratch because this user has a lot of permissions that would need setting up again.

Edit: in response to Philip Kelley's question, here's what I get when I run select * from sys.database_principals where name = 'username'.

Apologies for the size of the image, you'll need to open it in a new tab to view it properly.

Edit2:

Ok, I've dropped the existing LOGIN as suggested by gbn, and I'm using the following script to create a new LOGIN with same SID as the user.

CREATE LOGIN [UserName] 
WITH PASSWORD=N'Password1', 
DEFAULT_DATABASE=[DatabaseName], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF, 
SID=0x0105000000000009030000001139F53436663A4CA5B9D5D067A02390

It's now giving me the following error message, it appears that the SID is too long for the LOGIN's SID field.

Msg 15419, Level 16, State 1, Line 1
Supplied parameter sid should be binary(16).

Am I up the creek without a paddle?

Answer 1

sp_change_users_login is deprecated.

Much easier is:

ALTER USER usr1 WITH LOGIN = login1;

Answer 2

I found that this question was still relevant but not clearly answered in my case.

Using SQL Server 2012 with an orphaned SQL_USER this was the fix;

USE databasename                      -- The database I had recently attached
EXEC sp_change_users_login 'Report'   -- Display orphaned users
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'Password'

Answer 3

You have an orphaned user and this can't be remapped with ALTER USER (yet) becauses there is no login to map to. So, you need run CREATE LOGIN first.

If the database level user is

• a Windows Login, the mapping will be fixed automatcially via the AD SID
• a SQL Login, use "sid" from sys.database_principals for the SID option for the login

Then run ALTER USER

Edit, after comments and updates

The sid from sys.database_principals is for a Windows login.

So trying to create and re-map to a SQL Login will fail

Run this to get the Windows login

SELECT SUSER_SNAME(0x0105000000000009030000001139F53436663A4CA5B9D5D067A02390)