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

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?

sp_change_users_login is deprecated.

Much easier is:

ALTER USER usr1 WITH LOGIN = login1;

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'

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)

Through trial and error, it seems if the user was originally created "without login" then this query

select * from sys.database_principals

will show authentication_type = 0 (NONE).

Apparently these users cannot be re-linked to any login (pre-existing or new, SQL or Windows) since this command:

alter user [TempUser] with login [TempLogin]

responds with the Remap Error "Msg 33016" shown in the question.

Also these users do not show up in classic (deprecating) SP report:

exec sp_change_users_login 'Report'

If anyone knows a way around this or how to change authentication_type, please comment.

What kind of database user is it? Run select * from sys.database_principals in the database and check columns type and type_desc for that name. If it is a Windows or SQL user, go with @gbn's answer, but if it's something else (which is my untested guess based on your error message) then you have a different problem.

---

Edit

So it is a SQL-authenticated login. Back when we'd use sp_change_users_login to fix such logins. SQL 2008 has it as "don't use, will be deprecated", which means that the ALTER USER command should be sufficient... but it might be worth a try in this case. Used properly (it's been a while), I believe this updates the SID of the User to match that of the login.

Create a login for the user

Drop and re-create the user, WITH the login you created.

There are other topics discussing how to replicate the permissions of your user. I recommend that you take the opportunity to define those permissions in a Role and call sp_addrolemember to add the user to the Role.