Find solution
How to Restrict Database for One User in SQL Server 2008 R2?

Restrict SQL Server 2008 R2 User to restore only one database (but not others)

  • It is my understanding that a user has to be a "dbcreator" in order to restore databases. However, what if I want a specific user to restore only one specific database, but not gain access or restore any other database on the server? Doesn't "dbcreator" allow that user to essentially restore any database?

  • Answer:

    Log in as a server admin. Create a stored procedure with execute as owner that restores a particular database. Assign permission to execute it to the user. As for where to put that stored procedure, I'm not sure it's possible to restore the DB from which you are running the SP, in which case you might want to create a completely blank database for the only purpose of storing this stored procedure.

Tinkerbell at Database Administrators Visit the source

Was this solution helpful to you?

Other answers

I thought that you may be able to capture this with a server-level DDL trigger. However, RESTORE_DATABASE is an event I expected to see but is http://msdn.microsoft.com/en-us/library/bb522542.aspx, and whether you are restoring a database from scratch or using WITH REPLACE, the CREATE_DATABASE event is not fired. Of course even if RESTORE_DATABASE were a supported event, you wouldn't be able to conditionally prevent the actual restore, since create/restore database is not transactional and therefore cannot be rolled back (and DDL triggers are after triggers, not before/instead of triggers). I think you would only be able to audit this activity and then respond after the fact, and I'm sure you could do this with an audit specification, or extended events, or trace, maybe even event notifications, etc.

Aaron Bertrand

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.