How To Find and Change Owner of All Databases in an Instance in SQL SERVER

 

Sometimes, we can receive the below error.

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: 1.1.1.1]

The reason for this error may be that we have modified an owner’s password.  We can change all databases owner to solve this problem. First, we should list owner of all databases in the instance.

We can find owners with below script:

If you want to change all databases owner to sa you can execute the below script:

If you are using Always On Availability Group and you have secondary databases on this instance, the change of owner for secondary databases will not occur as a result of this script.

You may want read the other article about SSPI Handshake error. “SSPI handshake failed with error code 0x8009030c and The login is from an untrusted domain and cannot be used with Windows authentication”

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 *