6

This question has been asked before, but I didn't see any answer (https://stackoverflow.com/questions/6468813/assign-a-login-to-a-user-created-without-login-sql-server)

How does one map a login to a user, when the user was originally created using the "SQL User without login" User Type option in the GUI? I'm using SQL Server 2012

6

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.

2
  • 5
    "Relatively painless" only if the existing user doesn't have a slew of permissions, belong to a bunch of roles, etc. You may want to take an inventory of permissions and role memberships first. :-) – Aaron Bertrand Jun 24 '14 at 15:38
  • Ha! Good point. And definitely worth noting. I'll throw that as an edit in my answer. – Thomas Stringer Jun 24 '14 at 16:04
0

For some reason these types of users cannot be re-linked using "alter user" as stated in Thomas Stringer answer.

By inspection, the SSMS dialog shows "SQL user without login" when the sys.database_principals.authentication_type = 0 or 1 (and possibly others).

select * from sys.database_principals where name = 'TempUser';

Noting that brute force does not work either:

update sys.database_principals 
set authentication_type = 3 -- 3=Windows
where name = 'TempUser';

Error: Ad hoc updates to system catalogs are not allowed.

The need may come from an environment where for some particular database it is common to push only schema changes one direction and pull entire databases another:

  For Dev -> build -> stage -> prod only schema
  For prod -> Dev, entire DB (for debugging, extra logins on Dev)

The Databases themselves carry all users so that the diffs (e.g. Redgate) list stays near 0. Permissions on objects tend to be listed with each object for diff tools like RedGate. Also some users may be listed as the "execute as" of an SP.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.