Tell me more ×
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.
up vote 4 down vote favorite

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?

share|improve this question

2 Answers

up vote 2 down vote

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.

share|improve this answer
+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. – Aaron Bertrand Feb 10 '12 at 23:14
up vote 0 down vote

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.

share|improve this answer

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.