Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

First, you can't prevent a sysadmin from doing anything. :-) Any login that is a member of the sysadmin fixed server role operates outside the permissions system - they can do anything.

My first thought was to use a DDL trigger to stop the restore. Unfortunately, restoring a database is an operation that does not cause any triggers to fire (see this Connect item).

My next idea is to either have an alert on the restore event or have some job periodically looking at the msdb.dbo.restorehistory table so you can at least catch the restores quickly and work to fix them. Not ideal, I know.

share|improve this answer
    
Your right, its not idea. I appreciate you giving it some thought. –  Steve Nov 26 '13 at 21:43

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.

share|improve this answer
    
This gave me something to think about. I already use the script and check to make sure I am on the server. I was hoping to use this to prevent others as well. I like the idea about lowering my permissions in PROD. I will work on this and let you know how it goes. Maybe post some scripts for others if I get it to work. –  Steve Nov 26 '13 at 23:50
    
This is an excellent route to go. We give our normal account sufficient permissions to do basic tasks and then have an "Admin" account for those occasions when we specifically need sysadmin permissions. It adds a bit of pain having to start up SSMS under the admin account but it's easier than having to be added and removed from an AD group. –  Kenneth Fisher Nov 27 '13 at 16:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.