CODEWITHSUNDEEP
sqlsql-server
dingx
dingx

Reputation: 1671

SQL server 2012: failed to change Database owner

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?

Upvotes: 0

Views: 3316

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46233

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.

Upvotes: 1

Related Questions