SQL Auth user that can restore some (but not all) databases

Question

I'd like to setup an SQL Auth user in MS SQL 2005 that can restore some, but not all, databases in a particular instance.

I'm not sure if I should use Server Roles for this, since they would seem to apply to all databases, but Database Role membership doesn't seem right either (I don't want the SQL user to potentially 'lose' their restore ability if they restored a backup that didn't contain their database role membership).

How can I accomplish this?

Answer 1

You can't set up a user as such. This permissions sits above database/users at the server/login level

The login could have "dbcreator" which says:

...and can alter and restore their own databases.

Even using GRANT would be tricky if not impossible, say, to "GRANT CREATE ANY DATABASE"

Restore is, in a way, a drop and create. Or simply a create.

I'd suggest the best solution (but probably not what you want to hear...) would be to create your own stored proc in master that checks rights and issue the RESTORE command if the login is set up as a user in that DB

Example: sp_checkandrestore 'dbname', 'backupfile'