Is it possible for a non system admin to perform a restore operation for a specific database in sql 2005. I have a database owner who would like to be able to do restores as needed from their development database into the production one.
asked
Who Can perform a Restore operation - SQL 2005
surely you mean do restores from Production into Dev, otherwise they are writing over their live data with test data?
The database owner can restore.
As per BOL:
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.
