Change DB owner while it's being used?

Question

Hello I'm trying to change the owner of an active SQL 2014 DB using:

ALTER AUTHORIZATION ON DATABASE::[TargetDB] TO LoginNameOwner;

When I run it it seems to hang and notice causes Blocking so I cancel above statement.

While running above there are Sessions that are in the Suspended, Running, Runnable statuses.

Is it correct to say that you can't run this statement while it's in some other state than Sleeping?

Thanks in advance.

Answer 1

Thanks SeeyaXi-msft

Sounds like changing the owner the DB does need to be inactive/SLEEPING mode only?

Answer 2

Hi @techresearch7777777

>Is it correct to say that you can't run this statement while it's in some other state than Sleeping?

No. The DB owner can be changed while the DB is in use.
In addition, If your method still does not work, you can try the following two methods:

1. Using dbo.sp_changedbowner
   USE [TargetDB]
   GO
   EXEC dbo.sp_changedbowner @loginame = [LoginNameOwner]
   GO

2.Using SSMS
Go to SQL Server Management Studio >> Right Click on the Database >> Go to Properties >> Go to Files and select OWNER.

>When I run it it seems to hang and notice causes Blocking so I cancel above statement.

Using master.dbo.sysprocesses and sys.dm_exec_requests or sys.dm_tran_locks to check whether the process is deadlocked or blocked.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thanks SeeyaXi-msft

Strange I was able to eventually change the owner using my earlier mentioned above - ALTER AUTHORIZATION ON DATABASE::[TargetDB] TO LoginNameOwner;

But it only worked when I noticed all connections were in Sleeping status only.

I wonder why earlier my statement was hanging and caused Blocking while there were SUSPENDED, RUNNING, RUNNABLE Statuses?

Answer 4

Thanks SeeyaXi-msft.

Answer 5

I encountered the same or similar problem with a particular database.

In my situation there were CLR objects in the database and changing the owner deregisters the CLR_Appdomains.

I waited until the system was less active before running the script again that succeeded and the appdomain was unloaded.

I did make sure to call one of the dependent functions that make use of the CLR and ensure that it is working that registered a new appdomain.