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