Scripting SQL Server Database Owners for Disaster Recovery


By:   |   Updated: 2015-12-30   |   Comments (4)   |   Related: More > 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:

Query showing ownership

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:

The ownership query

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:

Using SQLCMD to build the ownership script

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


Last Updated: 2015-12-30


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, January 04, 2016 - 9:55:26 AM - K. Brian Kelley Back To Top

 

There are a number of potential security issues, which is why it's checked for in sp_Blitz:

 

http://www.brentozar.com/blitz/database-owners/

 

Follow the link to Andreas Wolter's write-up.

 

http://www.insidesql.org/blogs/andreaswolter/2014/06/sql-server-database-ownership-survey-results-recommendations

 

He does a good job explaining potential issues. 

 


Thursday, December 31, 2015 - 3:18:32 AM - Joe Doughles Back To Top

Hi Brian,

Can you provide any shortfall of having databases owned by sa? We disable sa login across all our environment, but we have sa as owner of all databases to avoid database ownership missing when the login is dropped or missing.

What login would do you use as database owner?


Wednesday, December 30, 2015 - 5:12:04 PM - K. Brian Kelley Back To Top

I never assume that to be the case. I have learned that when you assume, you get burned. 


Wednesday, December 30, 2015 - 4:39:09 PM - Narasimha Back To Top

As per my understanding on subjected titile as mentioned as DR , I suspect ownerships will be restored as part of Database restoration during the DR plan. I think more would be having logins information in an automated way.

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools