Sql-server – Prevent SysAdmin from doing a Database Restore

permissionsrestoreSecuritysql serversql-server-2008-r2

I am a sysadmin on my SQL Server 2008, and I need to be, but I would like to setup my security on the Production server to prevent me from accidentally restoring to the production database. I restored databases to a test machine often for developers to debug/test and while I am always really careful, after about ten years, today I wasn't.

My thought was to put in a DENY on the restore permission and if I ever need to really restore, remove the DENY, but I can't find anything like this.

Does anyone know how to do this or maybe have a better idea?

Best Answer

Greenstone Walker is right. If you are granted SA rights, there really isn't much to do there to prevent it. I like that you are worrying about protecting from your ability to make such a mistake. I've seen people burned by an "oops" restore before. Not pretty.

There are potentially a few things you can do, though. Your milage may vary but some ideas to get you thinking:

  • Use Scripts: Use restore scripts. Get in the habit of always using them. You can write and test restore scripts ahead of time. You could do something like check your @@ServerName and have some logic in there to require a positive action. Before it will work. Something like:

    DECLARE @ServerNameToRestoreOn VARCHAR(40)

    SET @ServerNameToRestoreOn = 'ChangeMe!' -- Servername\Instance

    IF @ServerNameToRestoreOn <> @@ServerName

    THROW 50001, 'Wrong Server!!!', 1

So this code may not be exactly what you do, but the point is - you are requiring a positive step to identify where you think you are and checking to see if you are where you think you are.

By using a Script you are also requiring yourself to type out items like the database you wish to restore, you are forcing yourself to look things over, pay attention to the SSMS window and see which server, etc. You are also preparing yourself for emergencies, by having a process, having a script and having an ability to use the same approach each time. Now when you have to restore and your CIO is standing behind you rapping her fingers on your cube wall repeatedly saying "is it done yet?" you don't have the added stress of doing the process a different way.

  • Lower Your Default Permissions In Prod Many financial institutions require this for compliance already. It is so much easier for a DBA to have SA rights, but in some organizations the DBAs don't have it. In some instances of this, you'll have a DBA_SA AD group. And that AD group is empty except for when someone truly requires DBA access.. So you could create a set of permissions for every day use and put a "DBA_Normal" AD group into that.. And when you truly require SA rights, or need to do a restore, etc. You could then be put into that group. Kind of a pain depending on what you do on a day to day basis, and depending on what that looks like, it may be too much of a burden to take, but something to consider.
  • Use a different account for restores - Create a SQL Authenticated account and always do restores as that account. In Production you can call it Prod_Restorer or whatever you like.. In Dev call it Dev_Restorer or whatever. Different passwords in each, and get into the habit of always using that to restore from.. Again a bit of a pain but something to consider.

I am sure there are other approaches you can take. I'd probably go with the script route, but again good sign to see someone concerned about preventing this.

Related Question