How To Change Owner of Database in SQL Server(ALTER AUTHORIZATION)

 

When a database owner changes his password, you may receive errors as follows. Therefore, it would make more sense to set the owner of the databases as the sa user.

SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed   [CLIENT: ]

You can set the owner of a database to sa using the following script.

You can do this with the script above, but Microsoft has announced that sp_changedbowner can be removed in the future. Instead, it recommends using the following script.

Change the owner of the database:

You can do more than just change a database owner with ALTER AUTHORIZATION. You can change the owner of each entity that has an owner.

Change the owner of the schema named test to DBO:

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *