Script to change database owner to sa for all user databases

There was a  SQL Server upgrade. All the user databases were restored from the older SQL Server instance. As a result,  the database owner of  the restored databases changed to the DBA’s logon ID . The original database owner was sa.

The DBA left the organisation and there account was deleted. Another DBA attempted to view the database properties through SSMS. An error appeared. This is a common issue, and it’s good practise to maintain a database owner which won’t create this issue.

This script will generate a statement for every user database on the SQL Server .  The statement selects all non-system database that are not in read-only status. Any attempt to change the database owner on a read-only database will throw an error.

To alter a SQL Server  database from read-only to read-write refer to the scripts found in this post - Make a database read only – READ_ONLY . There is also a script on how to change from read-write to read only.

When you’ve generated the script, execute the script on the SQL Server instance

SELECT 'use '+QUOTENAME(name)+ ' exec sp_changedbowner @loginame =''sa'' ' FROM sys.databases AS d WHERE d.database_id>4 and d.is_read_only = 0

In another post , I’ll discuss why it is not  good SQL Server security practise to use sa as a user account. My general policy is to disable the sa user account - to lessen the surface area for an attack.

Read more on SQL Server Security management

Powershell sql server security audit

Stored Procedure Checklist

SQL Server – How to manage Failed Logon attempts