Backup Permissions
I created a new user on the server that will access certain databases.
But when I go to backup or restore the database I get the error:
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.....................
The error shows for any other path in my system. Even those where the user and the Service Account has full control permissions.
BUT, if I type the full path and click ok, it complains that it can't show, BUT it does backup or restore the database. Just doesn't show the tree view for the path.
If I do the operation using the sa account, the dialog shows all paths without complaint.
PS: Already added user to the db_backoperator role.
What permissions are required?
Answer
db_backupoperator is a Database Role, not a Server Role or a Windows permission. It only grants the user necessary access to the database to make a backup. It does not grant any rights to the server's file structure, which are needed to actually make the backup file.
IIRC, to access the file structure to make a backup, the user must either already have windows/domain rights to access it, or have the Server Role sysadmin to pick-up the SQL Server's own Windows access rights.
Also, to actually Restore a database the user will need the Server Role dbcreator.