SQL Server Permissions – Grant User Permission to Restore Database

permissionsrestorerolesql serversql-server-2019

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.

Best Answer

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

Use TDE, which in particular encrypts .ldf and .mdf files.
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

Related Solutions

Sql-server – db_owner Member Can’t Delete on Tables

Unless the users is mapped to the sysadmin fixed server role (where they will connect to the database as the user dbo) then they can be limited by security on the database. (i.e. A member of db_owner will receive a security check where dbo will not.)

A quick test to see if it is a permissions issue is to temporarily grant a user sysadmin role and then see if they can delete data.... if not then I'd start looking for constraints on tables.

If they can then run the following against the database (you may get more you may need to add DELETE to your where clause):

select * from sys.database_permissions 
where state_desc = 'DENY'

If permissions are set it will give you something like:

class class_desc                                                   major_id    minor_id    grantee_principal_id grantor_principal_id type permission_name                                                                                                                  state state_desc
----- ------------------------------------------------------------ ----------- ----------- -------------------- -------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------
0     DATABASE                                                     0           0           10                   1                    DL   DELETE                                                                                                                           D     DENY
1     OBJECT_OR_COLUMN                                             1501607433  0           10                   1                    DL   DELETE                                                                                                                           D     DENY

(2 row(s) affected)

If deny delete is set at the DATABASE level then well then there is your problem. Adjust permissions so required users are not actually DENYed access.

SQL Server – How to Update Permission for Table Variable

Martin Smith's comment ("Does the subquery itself work?") was ultimately the answer and highlighted my insufficient forensic work! ;-)

The subquery was returning ROW COUNTS from tables:

...
INNER JOIN sys.dm_db_partition_stats AS ddps 
ON i.OBJECT_ID = ddps.OBJECT_ID
...

The fixed roles I was using don't give VIEW DATABASE STATE permission (which is required to view the db_partition_stats table). So, I'll either need to mess with the roles for these users or find another workaround.

I guess it's worth noting that if you come across this circumstance, you need to deconstruct your query. The fixed db roles that I refer to CAN CREATE, INSERT, UPDATE, and DELETE records from a table variable.