March 25, 2011 at 1:08 pm
OK, we set up a SQL Server 2005 proxy account to run a backup managament plan. The owner of the job is of course 'SA' but for the step that runs the plan, for 'Run as', we set it to use this new SQL Server proxy account.
So the question is, what rights do we have to give the proxy account? Do we have to assign it a server role or give it rights to the server itself? Also, we don't want to give this proxy account SysAdmin role or admin rights! As per best practice, we want to give this guy just the bare minimum it needs to do the backups.
Thanks in advance to all who reply!
March 26, 2011 at 9:51 am
Yes, grant the db_backupoperator role to that login.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 28, 2011 at 11:10 am
Thanks Jim! OK, I gave our proxy account 'backupoperator' rights to msdb but then came up with this error:
Executed as user: afh\sqlbackupadmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.5000.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:03:34 AM Could not load package "Maintenance Plans\PWCTR2 Nightly Backups" because of error 0xC001404A. Description: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_getfolder', database 'msdb', schema 'dbo'.). Source: Started: 7:03:34 AM Finished: 7:03:34 AM Elapsed: 0.125 seconds. The package could not be loaded. The step failed.
I also gave this guy role 'SQLAgentOperatorRole' and still this error appeared. Any other suggestions? Thanks in advance!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply