July 25, 2008 at 8:01 am
Subject says most of it.... while running SQL Agent job step that has an SSIS package getting permission denied on sp_send_dbmail. Step runs as "SQLAgent Service account". Specific error is:
"Executing the query "mysp" failed with the following error: "The EXECUTE permission was denied on the object 'sp_send_dbmail', 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."
I can run this as a job T-SQL job step without any problems:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Applications',
@recipients = 'me@work.com',
@body = 'test body',
@subject = 'mytest';
July 25, 2008 at 1:47 pm
Since this will run in the context of the SQL Agent user account, make sure that user has appropriate rights.
July 25, 2008 at 2:15 pm
I have a user "servername\SQLServer2005SQLAgentUser$servername$MSSQLSERVER"
It is mapped to sysadmin role by default. Both boxes are like this.
On properties
Connection option: alias is empty
SQL Server Connection: Windows Auth selected.
I have a dev system that the sp send db mail works ok with. The sql agent appears to be configured the same way there.
July 25, 2008 at 2:28 pm
Also on SQL Agent properties
Connection option: alias is empty
SQL Server Connection: Windows Auth selected.
I have a dev system that the sp send db mail works ok with. The sql agent appears to be configured the same way there.
I see I changed the startup account on both boxes to use like "sqlagent" for the service.
July 28, 2008 at 7:05 am
Check the surface area configuration to make sure that database mail is enabled on this box.
July 28, 2008 at 7:16 am
Yes dbmail is enabled. I can run a sp_send_dbmail in a regular SQL Agent job step, but when it runs inside a SSIS, that's when it gets the permission denied.
July 28, 2008 at 11:11 am
Are you using a send mail task inside the package or an execute SQL task?
July 28, 2008 at 11:23 am
jim.powers (7/28/2008)
Are you using a send mail task inside the package or an execute SQL task?
I need to run it in a SSIS package, but to prove that it is setup correctly and do not have permission problems I tested it in a SQL Agent T-SQL job step.
July 28, 2008 at 11:43 am
Can you check whether user that execute SSIS packagee and database mail procedure is member of DatabaseMailUserRole (Database Role) in MSDB database. if not just add the user and try .i had some kind of similar problem that was solved by adding user to this role member.
Just a try ...
July 28, 2008 at 11:44 am
Here is another conversation with a sample script that might help:
http://www.sqlservercentral.com/Forums/Topic452358-148-1.aspx
July 28, 2008 at 1:44 pm
While dreaming this past weekend, I thought about looking at profiler. Sure enough, I can see that SSIS step being run as the user that connects to the user database. I added that user into the databasemailuser profile.
Now, I get this error:
Message
Executed as user: WSQL\sqlagent. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:31:15 PM Error: 2008-07-28 15:31:15.99 Code: 0xC002F210 Source: Execute SQL Task Execute SQL Task Description: Executing the query "EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Web Applications', @recipients = 'me@work.com', @body = 'test body', @subject = 'mytest';" failed with the following error: "Could not obtain information about Windows NT group/user 'exportpro', error code 0xffff0002.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 3:31:15 PM Finished: 3:31:15 PM Elapsed: 0.5 seconds. The package executed successfully. The step succeeded.
July 28, 2008 at 2:22 pm
I did some searching on sqlservercentral.com and came across various links that point to MS KBs as well as forum messages saying just change it to sa account. Sure the SA account works, but is that safe (security wise)? Some of the MS KBs don't sound like they apply exactly, so I did not try them.
March 1, 2010 at 8:11 am
Sailor (7/28/2008)
I did some searching on sqlservercentral.com and came across various links that point to MS KBs as well as forum messages saying just change it to sa account. Sure the SA account works, but is that safe (security wise)? Some of the MS KBs don't sound like they apply exactly, so I did not try them.
hello,
this might help you http://www.sqlservercentral.com/Forums/Topic837947-149-2.aspx
It has similar problem and setting the Public profile to default works !
HTH,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply