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.