July 10, 2015 at 12:42 pm
My Assistant Director wants our group to allow users that own databases on our SQL Server 2008 R2 cluster to be able to backup their databases on demand. Does anyone else out there allow this? If so, how do you handle this process? I tested giving an account db_backupoperator permission, but it would fail when I clicked the ellipses for the backup location. I was thinking I could create a job and map the user to the MSDB database and give the user SQLAgentUserRole... then make them the owner of the job.
I tried to express my opinion that this was a very bad idea, but it fell on deaf ears. Thank you in advance for any advice!
July 10, 2015 at 12:51 pm
It would make more sense to me that they would be able to request for the database to be backed up.
If they must be allowed to back it up themselves, then having them execute the backup job sounds like the better approach to me.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 10, 2015 at 1:17 pm
Perhaps you could set something up where they update a flag in a table indicating that they want their database to be backed up. Then you set up a job that runs regularly, and backs up the requested databases, and resets the flag(s).
July 10, 2015 at 1:20 pm
Alvin Ramard (7/10/2015)
It would make more sense to me that they would be able to request for the database to be backed up.If they must be allowed to back it up themselves, then having them execute the backup job sounds like the better approach to me.
I agree with Alvin. I would even go so far as to suggest they can't run the job directly from SSMS. Instead create a utility application that execute the job. This keeps users out of the database where they have no business being.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2015 at 1:35 pm
Sean Lange (7/10/2015)
Alvin Ramard (7/10/2015)
It would make more sense to me that they would be able to request for the database to be backed up.If they must be allowed to back it up themselves, then having them execute the backup job sounds like the better approach to me.
I agree with Alvin. I would even go so far as to suggest they can't run the job directly from SSMS. Instead create a utility application that execute the job. This keeps users out of the database where they have no business being.
lptech (7/10/2015)
Perhaps you could set something up where they update a flag in a table indicating that they want their database to be backed up. Then you set up a job that runs regularly, and backs up the requested databases, and resets the flag(s).
Combining these two because I agree with both.
Users have no business being in the database performing DBA tasks.
If they must be able to get a backup, have them submit a request or worst case scenario, allow them to execute a stored procedure that updates a table as mentioned by iptech. Then the automated job reads that table and backs up any database that is flagged to be backed up.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply