Sql-server – why user with db_owner & db_BackupOperator database role is unable to restore database

backuprolesql serversql-server-2008-r2

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 .

Best Answer

"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.

Related Question