solving sql server problems for millions of dbas and developers since 2006



SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups SQL Server Events MSSQLTips Giveaways MSSQLTips Advertising Options

MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page












Problem with SQL Server 2005 Backup Job Maintenance Plan - Error Using Proxy Account

Asked 4/1/2011 3:42:05 PM by Pete

I created a SQL Server Maintenance plan to backup all our databases and store the files on a share.  The job owner is "SA" but the job step is set to "run as" a proxy account.  The proxy account's credential is a domain account that has the following database rights to MSDB - db_backuperator, db_dtsoperator, SQLAgentUserRole.

Now we obviously don't want to give the credential/domain account SysAdmin rights to the server (which BTW, allows this job to run without a hitch).  We just want to give the bare minimum, as per best practice.  So when we run the maintenance plan, the job fails and we get the following error message:

Message
Executed as user: XYZ\sqlbackupadmin. ...9.00.5000.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  6:00:00 PM  Error: 2011-03-31 18:00:11.60     Code: 0xC002F210     Source: {2012CDE4-7671-40B1-92F2-26A84187DCDA} Execute SQL Task     Description: Executing the query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp_maintplan_open_logentry '{1025AEF1-AC7C-48FB-9D44-C595BAF4CEBC}', '{845EB87D-0CB6-4E83-BFBD-298EA1B60FBD}',NULL, @Guid OUTPUT      Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The EXECUTE permission was denied on the object 'sp_maintplan_open_logentry', database 'msdb', schema 'dbo'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  Error: 2011-03-31 18:00:11.72     Code: 0xC0024104     Source: {E8DD7625-07DD-48C0-A98F-DE163EAE8A61}      Descript...  The package execution fa...  The step failed.

I've tried a variety of things like making the domain account (credential) db_owner for MSDB without any success.  Again, the only thing that seems to work is if I give the domain account (credential) SysAdmin rights which won't cut it for us.  Please advise.  I'm at the end of my rope with this...

Thanks in advance to all who reply!



Answered 4/1/2011 4:11:08 PM by Greg Robidoux (0) vote answer is useful vote answer is NOT useful

Do you have to use a Maintenace Plan to create the backups?

If not, just create a job that backups all of the databases.  Take a look at this tip: http://www.mssqltips.com/tip.asp?tip=1070

The other option is to try to GRANT EXEC permissions on sp_maint_open_logentry in msdb to see if that fixed this issue. 


Answered 4/5/2011 9:46:22 PM by Pete (0) vote answer is useful vote answer is NOT useful

Hi Greg - thanks very much for responding.  That link you sent did the trick.  I left the maintenance plan to do the cleanup of the old backups and then implemented that script to do the actual backups.  The step that fires the script is set to run as the domain account that we designated to perform backups.  So far so good.  Thanks again!

 


Answered 5/12/2011 1:03:16 PM by JoeM (1) vote answer is useful vote answer is NOT useful

take alook at this article

www.airbornegeek.com/tag/maintenance-plans


Answered 11/20/2012 5:17:51 AM by jagadish (0) vote answer is useful vote answer is NOT useful

Executing the query "DBCC SHRINKDATABASE(N'asdfadf', 10, TRUNCATEONLY)
" failed with the following error: "Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
DBCC SHRINKDATABASE: File ID 1 of database ID 10 was skipped because the file does not have enough free space to reclaim.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

i have a maintenance plan, when am trying to execute one step is failed. please suggest.



Post an Answer

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email   Notify for updates
Comments
*Enter Code refresh code


 
Sponsor Information
Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis!

SQL Developer Bundle: Cut out dull work with 12 tools for simpler, faster database development. Free trial

What grade do you think your SQL Servers get? Find out with Edgewood's Health Check consulting services.

In two mouse clicks view SQL bottlenecks. With ZERO impact pinpoint all poor performing SQL with 100% accuracy.

The SQL Server Security THREAT - It’s Closer Than You Think


Copyright (c) 2006-2012 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups
Some names and products listed are the registered trademarks of their respective owners.


Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com