0

I'm in sysadmin server role on the server . All Programmers are in dbCreator Server role , So they can create databases for themselves and they have in db_owner & db_Backupoperator database role for their own databases.

All programmer have remote desktop access to the server just like me . I would like every Programmer be able to backup and restore their own databases .

But When they are trying to restore , in Specifying back-up dialog , when user click on add button they get this error ( while i can restore ).

C:\Program files\Microsoft SQL Server\mssql10_50.sql2008\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

I appreciate any help .

2 Answers 2

2

"Their database" for the dbcreator means owner in the sys.databases.owner_sid sense
Not members of db_owner.

The actual rights needed are described under RESTORE (and my added bold)

If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).

RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

0

You need to check that the path where the backups exist is accessible for the user account used by the SQL Server service. Check permissions for the backup folder and its files and see if the SQL service account is in the list.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.