Yes, this is actually fairly easy to accomplish using Module Signing. With module signing, you create a module (in this case, a stored procedure) and grant the required permission to the module, not to user or login. You only grant the user permission to execute the module. And, by doing this, not only can you be granular enough to effectively only grant the ability to restore, by hard-coding the
RESTORE statement, you are essentially granting permission to only restore this particular database.
Detailed instructions, explanation, and walk-through demo can be found here:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level
But to simplify, here are the basic steps:
(we will assume that the stored procedure will be created in the
[master] DB, which means we don't need to copy the certificate to another DB. the stored procedure could just as easily be created in a utility database, in which case you just need to copy the certificate to
[master] as shown in the instructions in the linked blog post)
- Create a stored procedure. This can be as simple as just the
RESTORE statement for a specific DB, or it can accept an input paramater for
@DBName sysname and construct Dynamic SQL using that parameter value, this allowing for restore of any particular DB, or maybe a
@DbToRestore TINYINT parameter that is used in a
CASE statement allowing the user to select from a limited set of pre-defined database names to restore.
- Create a certificate specifying a password (do not rely on the Database Master Key / DMK). Do not give the end-user / customers this password (including a script containing the password).
- Sign the stored procedure (i.e. the "module") using that certificate and its password.
- Create a login (not user) from the certificate.
- Add the login to the
dbcreator fixed server role.
- If the end-user / customer does not already have a user in
[master] for their login: create a user in
[master] for the customer's login.
- Grant the customer's user
EXECUTE permission on the stored procedure.
For added security: it might be a good idea to prevent them from signing another module, or re-signing this one, if they somehow manage to get the password. This is not always necessary, but is probably a good idea here since you distribute the system to the customer.
- Backup the certificate to a file
- Remove the private key from the certificate
Without the private key, the certificate can only validate that a module was signed with it, but it can no longer sign anything. Since signing a module applies the permissions of the associated certificate-based login (i.e. the login that was added to
dbcreator), this prevents the customer from applying those permissions to their own modules, if they ever managed to create any. ALSO, if the customer manages to alter this stored procedure to do something that you are trying to prevent, the signature is dropped, meaning that the module no longer has the elevated permissions (even if they put the stored procedure back to the original code).
Please also take a look at the following answer of mine, also here on DBA.StackExchange, to a similar question:
Execute Permissions for a Store Procedure that creates databases