October 3, 2012 at 10:40 am
We have a group of people who directly edit table data in SSMS (SQL 2008 R2), but are not DBAs who should have sysadmin access. Naturally, they should backup these databases before editing them.
I've added the AD Group containing these users to the db_backupoperator Database roles for the DBs they work on, and that's fine, but I'd prefer they run the SQL Maintainance backup job that normally runs at night, because it will put the backup file in the appropriate folder with a timestamp where it will be automatically deleted after 3 days if the backup is not needed. It would also be easier for them - two clicks.
The way to enable this without allow them to run ANY Agent job would seem to be to add the AD group to the SQLAgentUser Role in MSDB, then make that group logon the owner of the approptriate Agent Jobs. But I cannot make an AD Group the owner of an Agent job.
So how can allow a GROUP of users to run certain Agent jobs without granting them access to run to ALL Agent Jobs?
October 5, 2012 at 5:49 am
It sounds like you've looked into the SQLAgentOperatorRole and decided it offers too many permissions ("enable this without allow them to run ANY Agent job").
One possible solution would be to write a stored procedure that calls sp_start_job to start the backup job. The stored procedure would be signed with a certificate with permissions to execute sp_start_job and your users would be granted execute permissions on that stored procedure.
Tutorial: Signing Stored Procedures with a Certificate
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 5, 2012 at 9:53 am
I thought of creating scripts or an SP that contains the backup commands and giving them execute rights on it, but my idea was to give them a easy GUI option, not one what requires them to locate & open the correct file or remember the right SP command to type in (there are 6 different databases on the same server they work with and have the occasional need to back up).
It looks like with SQL2008 R2 that's going to be right-clicking the database\Tasks\Back Up...
Is this changed in SQL 2012?
October 5, 2012 at 10:26 am
I do not think there have been substantial changes (if any) to the Agent Roles in 2012 but you can check it out:
SQL Server Agent Fixed Database Roles
The thing about the proc that is nice it is will run your job. At least that keeps things within your coded backup plans.
You could always teach them how to find the proc in the Object Explorer, right-click it and choose execute. If they are in a database by themselves and have no parameters (one proc per database) then it will be pretty easy to do.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 5, 2012 at 11:31 am
I've also thought of creating one proc that takes the database name as the parameter. Asking them to type something like
exec sp_manualbackup 'databasename'
would not be so bad for them.
It would be a bigger project for me, since it would accomodate up to 6 different databases on 6 different servers (dev, test and prod) which may not have the same back up file paths, so each instance would have a slightly different version of the proc.
This may be an example of me trying to come up with something cool, slick and time intensive when simpler solution meets the requirements.
October 5, 2012 at 11:38 am
You have a couple worlds colliding, data managers and backup operators, and you're trying to spoon-feed the data managers database backup capabilities. Which is fine, but data managers are not the intended audience for the database backup features available in SQL Server. You could go with a single proc and still show them how to use execute it via Object Explorer, they'll just be prompted to key in the parameter value. It would not be so bad of a solution in my opinion.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply