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

dbtut

6 years ago

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:

select Name,SUSER_NAME(owner_sid) from sys.databases

1	select Name,SUSER_NAME(owner_sid) from sys.databases

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

USE [?]
IF DB_ID() IN
(SELECT database_id AS dbname from sys.dm_hadr_database_replica_states where is_primary_replica=1
UNION ALL
SELECT database_id from sys.databases where database_id>4 
and database_id NOT IN (select database_id from sys.dm_hadr_database_replica_states))
BEGIN
 exec sp_changedbowner ''sa''
END
'

USE [?]

IF DB_ID() IN

(SELECT database_id AS dbname from sys.dm_hadr_database_replica_states where is_primary_replica=1

UNION ALL

SELECT database_id from sys.databases where database_id>4

and database_id NOT IN (select database_id from sys.dm_hadr_database_replica_states))

BEGIN

exec sp_changedbowner ''sa''

END

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”