SQL Server – Fix Error While Taking Backup Using SSMS

backupsql serversql-server-2008-r2ssms

I am using SSMS. I have about 5 logins. For a particular login I have all server roles other than sysadmin. It is a sql login (not windows authentication). In user roles it has all permission for almost all databases. For some database it has only datareader.

Now when I try to take backup of database using SSMS and when try to select backup destination by clicking button I get the following error:

"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\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.

If you know that the service account can access a specific file, type
in the full path for the file in the File Name control in the Locate
dialog box."

Even though I have db_owner and all permissions I am getting this error. But if I select the path manually in the file name field then it allows to take backup without any error.

So why does it happen? I want this user to take backup without these error messages. (But I can't give sysadmin for the user). How can I solve it?

I already some article stating that use SQL query and sqlcmd to take backup. But I want it to work using SSMS.

Best Answer

This is not a SQL Login permission issue. Rather that SQL Server Service Account can't access the location for the backup. To fix this you need to grant access in windows to that path to the account that the SQL Server Service executes under.