[sql-server] 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?

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'

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.

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.

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.

sp_change_users_login is deprecated.

Much easier is:

ALTER USER usr1 WITH LOGIN = login1;