Backup Permissions

Question

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 1

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.

Answer 2

Are you connecting using a SQL authentication login or a Windows login? If a SQL auth login, how are you giving that SQL login "full control permissions" to a folder in Windows? Windows has no idea about any SQL authentication logins you've created in SQL Server. Please show us exactly what you mean by "I created a user on the server" - what user? what server? SQL Server or Windows?

As a workaround, you could also create a stored procedure that executes as sa or a Windows login that is part of the sysadmin group, and give this lesser-privileged user the ability to execute. However I was able to backup a database by adding a peon user with no other permissions at all and simply adding them to the db_backupoperator role:

CREATE LOGIN peon WITH PASSWORD = 'foo', CHECK_POLICY = OFF; GO CREATE DATABASE splunge; GO USE splunge; GO CREATE USER peon FROM LOGIN peon; GO EXEC sp_addrolemember 'db_backupoperator', 'peon'; GO EXECUTE AS USER = 'peon'; GO BACKUP DATABASE splunge   TO DISK = 'C:\tmp\splung.bak' -- change this path obviously   WITH INIT, COMPRESSION; GO REVERT; GO 

So, I would validate that the SQL Server service account has sufficient privileges to write to the path in question. I know you said that this was the case but as I've shown this doesn't seem to be a problem with the peon user but rather the underlying engine's ability to write to the file system. If you try the above backup command without adding peon to the db_backupoperator role, you get this error (it doesn't let you get anywhere near the actual backup command or verify any permissions on the disk):

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

If this is a Windows login, then please validate that the user does, in fact, have write permissions to the folder in question. Try a different folder other than the hierarchy under C:\Program Files\... and don't try to write directly to the root (e.g. C:\file.bak).