Share via

sql server backup permision server wide role

Alen Cappelletti 1,047 Reputation points
2022-04-13T12:54:52.02+00:00

Hi,
Is it possible to create a server role with backup permission on all DBs?
I understand the possibility of a db_backupoperator or db_owner for every single database, but server wide?
Thanks Alen

SQL Server Other
Accepted answer
  1. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    2022-04-13T21:41:22.823+00:00

    Quoting Books Online:

    BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

    Which is a bit of a pity, as you may have a junior DBA that should be able run backups without being sysadmin.

    What you can do is to write a stored procedure that runs the backup commands needed, and where you check the input parameters. Then you sign that procedure with a certificate from which you create a login that you add as member to sysadmin. Finally, you grant permission on this procedure to the junior DBA.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.