2

In a database I created a user with the following roles:

Server Roles

  • public

Database roles

  • db_backupoperator
  • db_ddladmin
  • db_datareader
  • db_datawriter

The problem is I don't want to change the user's roles, but I do want grant the permission to restore the database.

Is there a workaround to only grant the permission to restore but not to drop nor alter the database?

Background info:

The company I work for created an app and they want the users of that app to have the permission to backup and restore the database, but not to create another database or drop the existing one. And, they don't want to have another user who has the role of db_owner, which would allow the customer to manipulate things as they please.

The price for the app is based on the numbers of employees a client has, so while installing the app on the client side they use a file to create a database where they specify all the details (including the number of employees), but they fear that someone might change the file and add the database multiple times, so they want to secure the instance by not granting any user the right to do anything. It's complicated but it is what they want.

0

You cannot do this on an SQL server level. But you can do this on OS level. Try to this route:

  1. Use TDE, which in particular encrypts .ldf and .mdf files.
  2. Instead of a native t-sql backup-restore (.bak). Make a backup-restore of the database files (.ldf and .mdf)

The user will still be able to delete the database, as he has access to physical files. But he will not be able to change, and even view the data, because the database is encrypted.

Pay attention!
Backing up .ldf and .mdf files have obvious drawbacks, you need to stop SQL Server, and there is no way to perform differential and incremental backups, etc

But there is a non-obvious drawback, information about such backups will not be stored in the system tables [msdb].[dbo]. [Backupmediafamily] and [msdb].[dbo].[Backupset]. And If the server runs incremental or differential backups, then restoring .ldf and .mdf files can lead to serious confusion in the backup chain

| improve this answer | |
  • If database service is running, you won't be able to delete mdf or ldf files. – Learning_DBAdmin Feb 21 at 5:07
  • @IvanGusev thank you , your response helped me a lot , i restored the database as mdf file and alter it after , it works like charm , too bad i can't give you any points – Gzina Feb 21 at 9:00
  • @Gzina, I got the dislikes right. It is impossible to advise backup .ldf and .mdf files without specifying flaws. I supplemented my post if you really will use backup .ldf and .mdf files, then please pay attention to the shortcomings. Sorry for my bad English. – Ivan Gusev Feb 22 at 0:00
  • thank you ,yeah I had to stop the instance in order to copy the files , and about the problems in your way to backup , i don't know if they need incremental or differential backups – Gzina Feb 24 at 8:17
5

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)

  1. 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.
  2. 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).
  3. Sign the stored procedure (i.e. the "module") using that certificate and its password.
  4. Create a login (not user) from the certificate.
  5. Add the login to the dbcreator fixed server role.
  6. 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.
  7. 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.

  1. Backup the certificate to a file
  2. 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

| improve this answer | |
0

In order to perform restore to an existing database dbcreator permission is required or the login should be member of sysadmin as mentioned in the Microsoft documentation.

Permissions

If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).

RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

ddladmin - Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database however can't restore database.

In case, you wish to understand difference between ddladmin and dbowner permission, please check this thread.

| improve this answer | |
  • thank for your comment , i already checked the documentation before and i am aware that it is impossible to grant him the the permission of restoring the database without him having dbcreator to create and alter etc , it has nothing to do with db_ddladmin what i am asking for is if there is a working around to get the things done – Gzina Feb 20 at 11:58
  • Welcome to DBA.SE. You should have mentioned this in your question that you are looking for work-around, please edit your question with your clear requirement. I don't think and know that there is any work around to bypass permission set by Microsoft. – Learning_DBAdmin Feb 20 at 12:04
  • 1
    I appreciate your taking the time to answer my question , i will edit my question – Gzina Feb 20 at 13:07
0

... grant the permission to restore but not to drop nor alter the database

Short answer: No.

In order to restore a database, you must first drop the existing one. There is no way to separate these responsibilities.

More importantly to me, though, why do you want to do this?

Most people don't understand restoring a database (Heck; most of them don't under what a database is!)

To most People, restoring a database is something like getting into their Delorean and hopping back to 1955 knowing that, when they arrive there, they will still know all about the Apollo moon landings, the Watergate scandal, ZZ Top and so on.
They fail to realise that their database will not travel with them but will, instead, be transformed into a native of 1955, blissfully ignorant of all these things and, worse still, they will have little or no way of getting [all] that lost data back!
(OK, you can do it with full-blown Oracle database, RMAN and a Recovery Catalog but that's not much help here).

The permission to obliterate data should be very tightly controlled and only given to those who actually know what they're doing with it.

And, as ever, always keep the Biggest and Best tools in the toolbox for yourself, so that you can clean up the mess that other people make.

| improve this answer | |
  • Thank you for replying , I Appreciate it . I will try to explain but forgive my bad english . I am a newbie in sql and sql-server and my workplace (IT company) they created an app and they want the users of that app to have the permission to backup and restore the db but not creating another db or droping the exisiting one , and they dont want to have another user who has the role of db_owner so he can manipulate it as he pleased . Thanks in advance – Gzina Feb 20 at 16:16
  • "they want the users ... to backup and restore the db" As I said, I would view this as a very Bad Idea because Users simply /do not/ understand what "restoring" a database actually does. Furthermore, I would push back and ask /why/ this capability is even needed. If it really does have to be there, then the Application should be carrying out this action on behalf of the users, using its own set of credentials that the Users are never aware of. – Phill W. Feb 20 at 16:37
  • Thank you for your response. i my self dont understand but i will say what they told me, they sell the app and the price differ from the numbers of employees the clients have, so while installing the app on the client side they use a file to create a database where they specify all the details (nbr of employess) but they fear that someone might change the file and add the database multiple times so they try to secure the instance by not granting any user the right to do anything . It's complicated but it is what they want, i suggested securing the file but they say no – Gzina Feb 20 at 16:56
  • If the database is under the control of the Users, then there's nothing you can do to stop them. They have to be able to do whatever they want with the database, because it's their Data. The Application needs to protect itself against this, for example /only/ using the database against which it is initially installed. If they choose to create more databases, they can do so, but the Application should totally ignore them. And the Users should be made aware of all of this. – Phill W. Feb 21 at 11:55
-1

An easy option would be to create a SSIS package that will do the restore, using a Proxy that uses an account with the appropriate permission. Then all you need to do is give the permission to the user to run that job.

| improve this answer | |
  • thank you for your answer , i am a newbie in sql server so i couldn't understand well your answer , if it is possible , can you elaborate your answer , thanks in advance – Gzina Feb 20 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.