1

I have a job that executes every 15mins on SQL Server 2008 and it comes up with the following error at least once in two days. But, it runs successfully the next time it runs. The account that is running the stored procedure has db_owner permission on the database. Any idea why?

The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'. [SQLSTATE 42000] (Error 229). The step failed.

Improve this question
6
  • have you looked to see if anything else is running at the same time as the failure? – DForck42 Dec 28 '11 at 20:32
  • yes, only log backups. – db7 Dec 28 '11 at 22:19
  • what is your job trying to do? i'm trying to understand more about the job so that i can have a better idea as to why your permissions are being "lost" sporadically. – SQLRockstar Dec 29 '11 at 1:36
  • sp_OACreate is a system stored procedure in the resource database (master) so execute permissions on this would not be implied by the db_owner role in a user database. The sysadmin role would have execute rights or any user/role that you grant execute rights explicitly on the stored procedure in the master database. – Jason Cumberland Dec 29 '11 at 16:54
  • 1
    Any way to find out what is running at that exact time as the job? It would appear that you have a conflict, perhaps within the resourcedb somehow, and this glitch is the result. Your permissions seem fine, given that it works all the other times, so my guess is that at that exact moment you are "locked down" for lack of a better term. – SQLRockstar Nov 17 '12 at 4:21
1

Check this link: http://msdn.microsoft.com/en-us/library/ms191188.aspx It allows you to enable Ole Automation Procedures. This change would affect the entire instance. This is turned off by default due to security.

Other Ole procedures are here: http://msdn.microsoft.com/en-us/library/ms190501.aspx

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.