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?


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.

| 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

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.

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

Just as Greenstone Walker said, you can't prevent a sysadmin from doing anything

However, if it is a login that is not a sysadmin you can prevent them restoring databases by denying them the privilege of taking backups of your databases and transaction logs.

USE <database_name>
DENY BACKUP DATABASE TO <database_principal>
DENY BACKUP LOG TO <database_principal>

No backup privilege = No restore privilege.

You can see more details on that, including examples following the link below:

what would be the right permission to allow everything else but overwriting or creating a database?

| improve this answer | |

I agree with all the answers posted before me that if one is granted sysadmin he/she can do absolutely anything. One thing to note that there is a fantastic feature in almost all rdbms that backup can be taken even if there are n number of users connected however restoration can be done if and only if nobody is using the database at the time you are trying to restore the database, mostly if it is a production database some or other users/service keeps connected and in order to perform successful restore you would need to stop these services or users spid and in doing so one can realise where is the restoration pointing to. So, from database providers perspective, they have already tried to handle this situation. Moreover backup file name should contain server name, database name along with date and time, even this helps.

In any case, it's always good to check server name before performing restore of database and script them will add further value to it.

I hope this helps.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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