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?

2 Answers 2


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.

  • +1 Alternatively to creating a blank database, you can put the procedure in master, model (which would allow you to execute it from tempdb) or in any utility database you have lying around. I actually thought about this solution while on a walk with my wife, and I'm glad someone posted it before I got back. I was thinking of this solely from the "prevent all except x" standpoint instead of the "allow only x" standpoint. Commented Feb 10, 2012 at 23:14

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 not (yet?) supported, 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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