Share via

sql server backup permision server wide role

Alen Cappelletti 992 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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,364 questions
Accepted answer
  1. Erland Sommarskog 107.2K Reputation points
    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