February 10, 2011 at 4:38 pm
I ran into the same issue. Options below to resolve your issue.
1) Make your sql server agant user account an sa
2) Give your sql server agent at least db_reader in the TempDB database
3) Add two line at the beginning and at the end
EXECUTE AS LOGIN = 'sa'
IF OBJECT_ID('tempdb.dbo.tmpErrorLog') IS Not Null
/*** The rest of the logic ***/
REVERT
I'm using option 3 and I'm not having issues any more. Thanks!
May 31, 2011 at 9:16 am
You shouldn't have to put the account that runs sql agent into the sysadmin role. It just needs to be a member of the DatabaseMailUserRole in msdb and a member of db_datareader in the db (or dbs) that it is reading data from when using sp_send_dbmail. If it's still not working with these permissions, you might need to enable Agent XPs.
http://technet.microsoft.com/en-us/library/ms178127.aspx
I'm amazed at how many threads I've seen for this issue that advise giving sysadmin rights to the domain account running sql server agent. That's overkill and creates a possible doorway into your db server that doesn;t need to be there.
November 27, 2012 at 1:38 pm
I am having the same exact problem. Works fine in SQL 2005, fails in SQL 2008 R2.
Run in Query window when logged in as the SQL Agent account, fails in job step.
February 14, 2013 at 6:29 am
The post from 1/5/2013 resolved the identical error I received when executing my dbmail script from within jobs. Thank you for posting the solution.
When I set the 'Run as user' to 'dbo' in the Job Step it runs without an error.
--Jeff
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply