patternsqlMinor
Is there a backup operator role in Microsoft SQL Server 2008?
Viewed 0 times
2008operatorsqlmicrosoftroleservertherebackup
Problem
I have a Windows Server 2008 with Microsoft SQL Server 2008 and VERITAS NetBackup installed. Basically, this NetBackup requires a valid logon ID into the SQL Server that is powerful enough to do a backup and restoration.
I have created a local ID on the OS called backup01 and assign it to the Backup Operator group. Next, I created a login in SQL Server 2008 with for backup01 with Windows Authentication. As for server roles, I assign it as 'public' and 'sysadmin'. With this, the backup and restoration via NetBackup will always work.
However, our security policy discourage the use of sysadmin or administrator equivalent rights to be assigned to non-administrator users.
In this case, where can I find this backup operator role in Microsoft SQL Server? Or is there any guide or tutorial that walks you through on creating a new role? Thank you!
I have created a local ID on the OS called backup01 and assign it to the Backup Operator group. Next, I created a login in SQL Server 2008 with for backup01 with Windows Authentication. As for server roles, I assign it as 'public' and 'sysadmin'. With this, the backup and restoration via NetBackup will always work.
However, our security policy discourage the use of sysadmin or administrator equivalent rights to be assigned to non-administrator users.
In this case, where can I find this backup operator role in Microsoft SQL Server? Or is there any guide or tutorial that walks you through on creating a new role? Thank you!
Solution
It is not a system level role, it is assigned in each database. Assign the login "public" and "db_backupoperator" roles in each database it needs to backup.
http://www.sqlbackuprestore.com/backupandrestorerights.htm
http://www.sqlbackuprestore.com/backupandrestorerights.htm
Context
StackExchange Database Administrators Q#28472, answer score: 9
Revisions (0)
No revisions yet.