Backup rights

If you are a member of the SQL Server sysadmin fixed server role, you can back up any databases on your SQL Server instance.  You can also back up the databases for which you are the owner of.  If you do not have backup rights to a specific database, the following error message is displayed when you attempt a backup:

Server: Msg 262, Level 14, State 1, Line 1 BACKUP DATABASE permission denied in database 'AdventureWorks'. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.

The backup failure is also recorded in the SQL Server log and the Windows Event Log.  However, the user that initiated the backup is not identified.

The sysadmin will need to grant BACKUP rights to the user.  You can do this using the following script, when connected to the database you want to grant the backup rights to:

GRANT BACKUP DATABASE TO [YOHZ_LABS01\YEOH6406]

You can see who has been granted or denied backup rights by running the following script when connected to the database concerned:

SELECT b.name, CASE a.protecttype WHEN 204 THEN 'GRANT_W_GRANT' WHEN 205 THEN 'GRANT' ELSE 'DENY' END AS rights FROM sysprotects a INNER JOIN sysusers b ON a.uid = b.uid WHERE a.action = 228 ORDER BY b.name

Another way to grant backup rights is by making the user a member of the db_backupoperator role e.g.

sp_addrolemember 'db_backupoperator', YOHZ_LABS01\YEOH6406'

or using SQL Server Management Studio e.g.

Users with the db_backupoperator role are also granted rights to run the CHECKPOINT and the DBCC commands.

Restore rights

Again, a SQL Server sysadmin has rights to restore a database from any backup sets.  For other users, the situation depends on whether the database already exists.  The following message is displayed when you have inadequate rights to perform the restore:

Server: Msg 3110, Level 14, State 1, Line 1 User does not have permission to RESTORE database 'backuptest'. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

If the database does not exist, you need to have the CREATE DATABASE rights to restore a new database.  The sysadmin can grant those rights by running the following script:

GRANT CREATE DATABASE TO [YOHZ_LABS01\YEOH6406]

If the database already exists, the user can perform the restore if he is a member of the dbcreator fixed server role, or is the database owner.