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?
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.
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.