Convert SQL Server user based on login to contained database user based on Windows account

Question

Context

We've recently changed our SQL Server security governance to favor users that are:

• Contained database users, and;
• Based on Windows domain accounts

It's working great for new projects.

I need to migrate several legacy databases, however, which have two other user types:

• Users based on Windows authenticated logins
• Users based on SQL Server authenticated logins

For practical purposes, I can just recreate the first type; they're for end users who don't own any objects, and whose permissions are all assigned by roles. They're easy to script.

Goal

My challenge is migrating the second type. These "app admin" users own all of the objects that the end users read/write. I know I can reassign ownership using ALTER AUTHORIZATION, but they also have a gnarly mess of delicate permissions, partially granted by us and partially by the respective app vendors. Reassigning those permissions to new users is possible, but would require a nontrivial investment in migration scripting, and would invite more risk.

Ideally, I'd like to convert the existing database users based on SQL Server authenticated logins directly to contained database users based on Windows domain accounts. But I can't figure out how to do it. Or if it's even possible.

Attempts

So far I've tried:

• ALTER USER [based_on_ss_auth_login] WITH LOGIN = [domain\account]

I know the WITH LOGIN syntax seems contradictory to my goal, but take my word that it's the appropriate, albeit confusing, syntax for creating a contained user based on a Windows account (see T-SQL doc for CREATE USER and reference section titled Users based on Windows principals without logins in master).

This attempt yields the error:

Msg 33017, Level 16, State 1, Line 6
Cannot remap a user of one type to a login of a different type. For example, a SQL user must be mapped to a SQL login; it cannot be remapped to a Windows login.

I also tried:

• EXECUTE sp_migrate_user_to_contained
• Followed by the ALTER USER statement above

The first step worked - my user was still SQL Server authenticated, but now at the database level (contained) instead of the instance level (login). Unfortunately, trying to further convert it to use Windows authentication raised an error:

Msg 33016, Level 16, State 1, Line 6
The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

My question is:

• QUESTION: Is there any way to convert a user that is based on a SQL Server authenticated login, to a contained database user that is based on a Windows account?

Again, I'm looking to directly convert the existing user so I don't need to reassign ownership/permissions/etc.

Thank you for any help you can provide.

Answer 1

This might be what you need:

sp_migrate_user_to_contained

Converts a database user that is mapped to a SQL Server login, to a contained database user with password. In a contained database, use this procedure to remove dependencies on the instance of SQL Server where the database is installed. sp_migrate_user_to_contained separates the user from the original SQL Server login, so that settings such as password and default language can be administered separately for the contained database. sp_migrate_user_to_contained can be used before moving the contained database to a different instance of the SQL Server Database Engine to eliminate dependencies on the current SQL Server instance logins.

Note

Be careful when using sp_migrate_user_to_contained, as you will not be able to reverse the effect. This procedure is only used in a contained database. For more information, see Contained Databases.

• manniongeo: Per the OP, I'm trying to convert to a contained user based on a Windows account. This procedure only appears to be able to migrate to a user with a password stored in the contained database. See details in "Attempts" section of OP, plus first sentence of the doc referenced in this proposed solution: "Converts a database user that is mapped to a SQL Server login, to a CONTAINED DATABASE USER WITH PASSWORD." (caps for emphasis; not shouting). Is there a way to use this procedure to switch to a Windows-authenticated user?

Answer 2

Yes.

You need to separate LOGIN and USER. A USER is an entity of a database. LOGIN is an entity of the server.

So assuming you have a USER "john" on the database "mydb". That user has also a LOGIN "john" with SQL Server authentication.

Create a second login based on Windows authentication:

create login company\john from windows

Now you have two logins: "john" and "company\john_smith".

Now you switch to the database, and assign the USER to a newly created LOGIN:

use mydb
alter user john with login = company\john_smith

And that is it.

Now, if a person tries to login as "john" to the server - he succeed, but he would no longer have access to the same objects as before in the "mydb". At the same time, he would remain a user in other databases if you have them on the server. You would need to either alter users on all the databases or just drop that users from other databases.

After that you can drop the old login.

You can look in the system tables sys.syslogins which exist only on master database. And each database has its own copy of sys.sysusers table. You can join them on sid field. This way you can confirm for yourself if a user in a database associated with a correct login.

• manniongeo: Per the OP, I'm trying to create a contained database user. The proposed solution creates a user based on a login.