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:
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:
You can see who has been granted or denied backup rights by running the following script when connected to the database concerned:
Another way to grant backup rights is by making the user a member of the db_backupoperator role e.g.
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.
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:
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:
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.