Menu

SQL Server

Database Permissions – SQL Server

Backup

To perform SQL Server full, differential, and transaction log backups, the SqlBak database user needs the following. Run the commands below (replace your_database with the actual database name; replace your_password with a strong password; repeat the USE/CREATE USER/ALTER ROLE block for each database you want to back up).

CREATE LOGIN sqlbak WITH PASSWORD = 'your_password';

USE your_database;
CREATE USER sqlbak FOR LOGIN sqlbak;
ALTER ROLE db_backupoperator ADD MEMBER sqlbak;

What each privilege is used for (backup)

  • db_backupoperator — Allows the user to run BACKUP DATABASE and BACKUP LOG. This fixed database role is the minimum required for full, differential, and transaction log backups. Add the user to this role in each database you want to back up.

Note: The SQL Server instance must use SQL Server and Windows Authentication mode (mixed mode) for the login to connect. You can change this in Server Properties → Security. Restart the SQL Server service after changing the mode.

Restore

To restore SQL Server databases, the SqlBak login needs the following server-level privilege. Run the command below (replace sqlbak with your SqlBak login name if different).

ALTER SERVER ROLE dbcreator ADD MEMBER sqlbak;

What this privilege is used for (restore)

  • dbcreator — Allows the login to create databases. Required for RESTORE HEADERONLY (which reads backup metadata) and for restoring into a new database. This fixed server role is the minimum required for these restore scenarios.