Permissions Required to Take Database Backup in SQL Server
For a user to take database backups in SQL Server a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role. Else you will receive the below mentioned error while performing backups.
Error Message
Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database 'DatabaseName'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Sample TSQL Script to Create Login with DBCREATOR Server Role and DB_OWNER Database Role
CREATE LOGIN BackupRestoreAdmin WITH PASSWORD='$tr0ngP@$$w0rd'
GO
CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin
GO
EXEC sp_addsrvrolemember 'BackupRestoreAdmin', 'dbcreator'
GO
EXEC sp_addrolemember 'db_owner','BackupRestoreAdmin'
GO