SQL Server Performance Forum – Threads Archive
Specific database restore rightsI have a multi-database server. The user needs to be able to restore from a backup (of a different database) over a particular database on an ad-hoc basis. The application they use allows them to do this by prompting for backupfile path and database name to resore to .
Giving them the dbcreator role expands this capability too far i.e. they could scrap any of the databases on the server. I would somehow like to restrict them to a specifc database name to restore to. Aletrnaive approaches just using TSQL/procs/batch files could be considered. Thanks in advance.
How about allocating fixed database role of of the db_owner or db_backupoperator. To restore a database, login should have CREATE DATABASE permissions if the database being restored does not exist. If the database being restored does exist, they should have RESTORE permissions granted, or should be a member of the sysadmin or dbcreator fixed server roles, or need to be the owner (dbo) of the database.
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
I think my problem stems from the fact that the application does a drop first if the database to restore to exists. This is why I have resorted to the dbcreator role. But server roles provide the same power over the other databases, this is what I don’t want. (SQL7 so no multiple instances)
Set up a table with permissions to what each user can restore. Set up a queue table to accept pending requests. You can just have a job set up that will hit the table every 15 minutes or so. The table will check for pending jobs and the user the job is running under will restore the database. This kind of give you the best of both worlds. You can provide the functionality and not compromise any of your security. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
[<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />]This could have legs, cheers, I’ll see what I can knock up for the user to populate the table.