0

I have an existing database, that i want to restore from a backup by overriding the existing database.

at the moment I have

db_owner
db_backupoperator 

roles

I thought that would be enough to restore the db. However when i try i get the error

User does not have permission to RESTORE database 'XY'

Is this achievable just with granting database roles (and without granting server role and fine tuning permissions?)

3

It is not. See https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15#permissions for detailed reference.

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.

| improve this answer | |

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.