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.
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,
Of course even if
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.