SQL server 2012: failed to change Database owner

0
votes

The original owner of the Database has left the company, so I want to change the owner to myself, however, it failed.
When I tried through SSMS: Database properties->Files->Owner, it give the error message like:

Set owner failed for Database XYZ.
An exception occurred while executing a Transact-SQL statement or batch.
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

And when I tried through script with the query:

ALTER AUTHORIZATION ON DATABASE::XYZ TO [MyUserName]

The query seems blocked and run forever without success.
Can anybody give some help?

1
sqlsql-server

1 Answers

1
votes

Queries using dbo-owned objects will acquire a schema stability lock on the existing database owner principal. The ALTER AUTHORIZATION will need a schema modification lock on the same principal, and are thus blocked due to the incompatible lock. You can query sys.dm_tran_locks to identify the blocking sessions.