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.
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.
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:
- Followed by the
ALTER USERstatement 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.