Script to change database owner to sa for all user databases

06 January,2015 by Jack Vamvas

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

 

 

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Script to change database owner to sa for all user databases


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer