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

user_39277 0
Viewed 139191

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'.

SQL User

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?

sql-server-2008 sql-server
6 Answers

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.

user_32662260
Related