神刀安全网

Scripting SQL Server Database Owners for Disaster Recovery


By:K. Brian Kelley |  Read Comments|   Related Tips: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:

Scripting SQL Server Database Owners for Disaster Recovery

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:

Scripting SQL Server Database Owners for Disaster Recovery

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:

Scripting SQL Server Database Owners for Disaster Recovery

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 Update:

原文  https://www.mssqltips.com/sqlservertip/4133/scripting-sql-server-database-owners-for-disaster-recovery/

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Scripting SQL Server Database Owners for Disaster Recovery

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮