I am using Sql Server 2008 and would like to create a user login that can only have a readonly view of the data, create backups and restore the database.
The first two I can do fine, using db_datareader, db_datadenywriter and db_backupoperator. I have problems with the restore however. From what I have read, you have to make the user database owner (cannot do this, user is not allowed to change the data structure) or add the role db_creator(cannot do this as it allows the user to drop other databases on the server).
This must be a common requirement. How do others solve this problem? Why isn't there a db_restoreoperator?