You may come across a request to set an existing user in a database to be the owner. The user is being used by an application and the application cannot have an outage.
The main issue here is that the database user has to first be dropped from the database before it can be made the owner. I’m not talking about the db_owner database role, but rather the actual database owner.
This begs the question of does the existing connection drop when the user is removed from the database?
To test this, below is a user called youruser which is a member of the db_owner database role in your_database_1.
Start a long running query using youruser in the database.
From another session drop youruser from the database.
As soon as you do this, the youruser query fails.
Hmmm, so how do you get around setting this user as the owner without stopping the application?
One way is to temporarily elevate the youruser privileges e.g.
-- Temporarily elevate the youruser privileges ALTER SERVER ROLE [sysadmin] ADD MEMBER [youruser]; -- Remove youruser from the database USE [your_database_1]; -- Note you may need to drop youruser's schema in the database prior to removing youruser -- DROP SCHEMA [youruser]; DROP USER [youruser]; -- Make youruser the database owner ALTER AUTHORIZATION ON DATABASE::[your_database_1] TO [youruser]; -- Revoke the Temporarily elevated privileges ALTER SERVER ROLE [sysadmin] DROP MEMBER [youruser];
And there you have it, the query has not stopped and the owner has been successfully changed.
