question

techresearch7777777-7743 avatar image
0 Votes"
techresearch7777777-7743 asked techresearch7777777-7743 answered

Change DB owner while it's being used?

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.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@techresearch7777777

Glad to know that your issue has been resolved, please kindly click "Accept Answer" and upvote it, if the response is helpful. It could help other community members looking for similar queries, thanks in advance!

0 Votes 0 ·
techresearch7777777-7743 avatar image
0 Votes"
techresearch7777777-7743 answered SeeyaXi-msft edited

Thanks SeeyaXi-msft

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @techresearch7777777-7743,

Not necessarily.
The status of the task is related to the resource status when your sql statement is executed. Other tasks will also go to other states due to some other conditions, such as RUNNABLE. So you may see more than SLEEPING states. But as long as there is no problem with the execution of the statement, if an error is reported, you can refer to the status to see if there is an accident like a blockage.

Best regards,
Seeya


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SeeyaXi-msft edited

Hi @techresearch7777777-7743

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.
98134-s.png

W

hen 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.



s.png (35.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

techresearch7777777-7743 avatar image
0 Votes"
techresearch7777777-7743 answered SeeyaXi-msft commented

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @techresearch7777777-7743,

SLEEPING: This process is currently not doing anything and is waiting for further instructions. Therefore, the statement can be executed normally.
RUNNING:The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. This session is another session, so when you execute a statement, you have to wait.
SUSPENDED, RUNNABLE: There may be problems with more than one of these states, which may block and need to be checked with the DMV.
In addition, SUSPENDED focuses on what caused the suspension by looking at the last SQL statement executed in the monitor, and using the profiler to query the running SQL statement.

Best regards,
Seeya

0 Votes 0 ·
techresearch7777777-7743 avatar image
0 Votes"
techresearch7777777-7743 answered

Thanks SeeyaXi-msft.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.