June 4, 2008 at 12:04 pm
We're new to SQL 2005 (at SP2). We'd like one of our Accountants to Execute a very simple Monthly Backup Maintenance Plan each month after they balance the books. This User has the Studio installed on the PC, but does not see the Management folder.
We'd prefer not to give full admin rights/permissions to this User. Is there a way to give the User access to the Plans (or a single Plan), without granting deeper rights?
Thanks in advance for your help.
Bill
June 4, 2008 at 12:19 pm
You can call the agent from the command line. Put that behind an icon on their desktop.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2008 at 1:23 pm
Is this just execution?
I'd script out a call, could be a stored procedure to sp_start_job with EXECUTE AS, or it could be an insert into a table, and then a job that checks the table every minute, and then starts the job.
Give them an icon as Grant mentioned that calls SQLCMD with the script.
June 5, 2008 at 4:25 am
I think that the security setup is easier if you create a normal T-SQL job that performs the backup.
BACKUP DATABASE MyDatabase TO DISK = 'C:\MyDatabase.bak'
Then you only need to grant the user SQLAgentUserRole in the msdb database, db_backupoperator in the user database and finally make the user the owner of the job.
If you would like to have a non SysAdmin run a Maintenance Plan then you need to create a Credential and a Proxy account and grant the user access to the Proxy account. The job step should run under this proxy account. The user should be the owner of the job.
You could run sp_start_job with EXECUTE AS LOGIN in a stored procedure, but then you need to grant IMPERSONATE rights on this login.
Ola Hallengren
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply