Scripting SQL Server Database Owners for Disaster Recovery

By: K. Brian Kelley   |   Updated: 2015-12-30   |   Comments (4)   |   Related: > Disaster Recovery

Problem

In our disaster recovery planning, we noted that not all of our SQL Server databases are owned by the sa account. I know this isn't a good practice, but in addition to that reason, some of our databases have to be owned by particular accounts. Is there a way to script this, so that we can set the proper owners for databases recovered in a disaster?

Solution

Indeed there is, for the sys.databases and sys.server_principals catalog views contain all the information we need.

Setup

First, let's set up an example so we can see these views at work:

-- Setup script where there are different database owners

CREATE DATABASE Test1;
GO 

CREATE DATABASE Test2;
GO 

CREATE DATABASE Test3;
GO 

CREATE LOGIN TestLogin1 WITH PASSWORD = 'MakeItAStr0ng0ne!';
GO 

CREATE LOGIN TestLogin2 WITH PASSWORD = 'MakeItAStr0ng0ne!';
GO 

CREATE LOGIN TestLogin3 WITH PASSWORD = 'MakeItAStr0ng0ne!';
GO 

ALTER AUTHORIZATION ON DATABASE::Test1 TO TestLogin1;
GO

ALTER AUTHORIZATION ON DATABASE::Test2 TO TestLogin2;
GO

ALTER AUTHORIZATION ON DATABASE::Test3 TO TestLogin3;
GO

We've created three logins, three databases, and set those three databases to be owned by the newly created logins. If we then query the catalog views, we'll see the ownership specified.

SELECT D.name AS 'Database', P.name AS 'Owner'
FROM sys.databases D
  JOIN sys.server_principals P
    ON D.owner_sid = P.sid
ORDER BY D.name;
GO

This produces the following result:

Scripting SQL Server Database Owners

What we want to do is write a query that builds the execution script for us. That gives us the option of running it if we have to restore the entire server, all databases, intact or to highlight and execute the individual ownership commands for databases that we do have to restore. The following script will do just that:

-- Generate script to alter the ownership of the databases
SELECT 'IF EXISTS(SELECT name FROM sys.server_principals WHERE name = ''' 
  + P.name + ''') ALTER AUTHORIZATION ON DATABASE::[' + D.name +
  '] TO [' + P.name + '];
GO'
FROM sys.databases D
  JOIN sys.server_principals P
    ON D.owner_sid = P.sid
WHERE D.name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY D.name;

And if we execute it (switching the results pane to text first), we'll see T-SQL to change the ownership of each user database:

Automating the Scripting Query

Likely you'll want to run this script regularly, say weekly or even nightly. The way to automate the results is to save the query into a .sql file and then use SQLCMD to execute the query and save the results. Here's an example of running SQLCMD against the local server using Windows authentication. Note the -i and -o switches to specify the input file (containing our query) and the output file (the scripted owners) respectively:

You can use this technique in whatever job scheduler you have, whether it be SQL Server Agent or something more robust. The key is to have the files written so that the servers can easily be identified by the script name and to store the scripts in a location that gets backed up for a disaster situation.

Next Steps

• Read up and learn how to script out the logins for a particular SQL Server instance.
• Think about these key areas to ensure your disaster recovery plan is fleshed out and up-to-date.
• Make sure you're getting the backups and restores right.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips