InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 20:00:16 GMT20http://www.sqlservercentral.com/Forums/Topic1302747-391-1.aspxThanks for this, put me on the right track. but I had to change the database in the drop down to MSDB rather than master or any other. my query just called fully qualified stored proc names. similar to belowEXEC msdb.dbo.sp_send_dbmail @profile_name='mickey profile',@recipients='mickey.mouse@disney.com;',@subject='Subject of email',@query= 'print ''Description of email''print ''''exec DBNAME.dbo.SPNameprint ''''print ''---------''etcetc'Wed, 19 Dec 2012 07:57:04 GMTrichard.austinhttp://www.sqlservercentral.com/Forums/Topic1302747-391-1.aspxI think you could have also used@execute_query_database = Product_Databaseinstead. it would have caused the sp_send_dbmail to change the database before executing the query.Mon, 21 May 2012 09:41:39 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1302747-391-1.aspxOK... I figured it out... and it was ridiculously simple.As I mentioned, the query that is being run is pretty straight-forward and simple. Here's a slightly modified version (to protect super-secret company info :cool: ):[code="sql"]declare @BodyText nvarchar(max)SET @BodyText = 'Attached is the quarterly audit.Regards,SQL Administrators'EXEC msdb.dbo.sp_send_dbmail@profile_name = 'SQL Mail',@recipients='joe.schmoe@company.com; billy.bob@company.com',@copy_recipients='admins@company.com',@subject='Quarterly Audit',@body=@BodyText,@query='USE Product_Database SELECT SUBSTRING(description, 1, 30) as Name, CASE WHEN privilege = ''1'' THEN (''Admin'') WHEN privilege = ''0'' THEN (''System User'') ELSE '''' END AS ''Role'' FROM [dbo].[user_table] WHERE enabled = ''1'' AND item_type = ''7765'' ORDER BY Role',@attach_query_result_as_file = 1,@query_attachment_filename = 'User_Accts.txt',@exclude_query_output = 1;[/code]So, the key here is the '[b]USE Product_Database[/b]' line. In the Job Step Properties page, I was leaving the "Database:" option as "master", which is what it defaults to. I figured that was fine, since the code explicitly states to use the necessary database.On all the other servers, this worked without any problems. On [b][i]this [/i][/b]particular server, though, I had to switch the "Database:" option to Product_Database, even though it seems a bit redundant since the code does so as well.Regardless, it worked like a charm. My guess is that it's some esoteric permission issue of some type, and since I call [b]sp_send_dbmail[/b] [u]before[/u] I switch to the Product_Database, that's what is causing the problem.Anyhow, thanks for the help, everyone.Mon, 21 May 2012 09:11:43 GMTsickpuphttp://www.sqlservercentral.com/Forums/Topic1302747-391-1.aspxsince it's just this one server,could it be something like database mail is not emabled, even though you've created the profile?it sounds like maybe you scripted the database mail settings out and ran them, but not the configure?[url]http://msdn.microsoft.com/en-us/library/ms191189.aspx[/url][code]use mastergosp_configure ''show advanced options'',1goreconfigure with overridegosp_configure ''Database Mail XPs'',1--go--sp_configure ''SQL Mail XPs'',0goreconfigurego[/code]Mon, 21 May 2012 08:25:43 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1302747-391-1.aspxthere isnt any sort of impersonation going on is there, like EXECUTE AS LOGIN?Mon, 21 May 2012 08:19:26 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1302747-391-1.aspxStill no luck...I ran:[b]EXECUTE msdb.dbo.sysmail_add_principalprofile_sp@profile_name = 'SQL Mail',@principal_name = 'COMPANY\xxxxxxxx',@is_default = 0 ;[/b]And got the same error.Anything else I might be missing?Mon, 21 May 2012 08:09:24 GMTsickpuphttp://www.sqlservercentral.com/Forums/Topic1302747-391-1.aspxAnother possibility, you do not have permission to use mail profile?according to http://msdn.microsoft.com/en-us/library/ms190307.aspx[quote]Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.[/quote]To grant permission [code="sql"]EXECUTE msdb.dbo.sysmail_add_principalprofile_sp@profile_name = 'Email_Profile',@principal_name = 'UserORrole'[/code]Fri, 18 May 2012 13:32:17 GMTDaxesh Patelhttp://www.sqlservercentral.com/Forums/Topic1302747-391-1.aspxNah, no DENY on sp_send_dbmail...Fri, 18 May 2012 13:08:55 GMTsickpuphttp://www.sqlservercentral.com/Forums/Topic1302747-391-1.aspxCan you run following under msdb and see if you find DENY on sp_send_dbmail and GranteeName is not the user or any associates role?If you have GRANT and DENY, DENY will take precedence[code="sql"]SELECTdp.class_desc, dp.permission_name, dp.state_desc,ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.nameFROM sys.database_permissions dpJOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_idJOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id[/code]Fri, 18 May 2012 12:53:47 GMTDaxesh Patelhttp://www.sqlservercentral.com/Forums/Topic1302747-391-1.aspxOK, so I'm trying to send an e-mail from within a job, using sp_send_dbmail. The job is pretty simple - just query a table and send the results as an attachment in an e-mail.The job works on all other servers except for one.On this particular server, I get the following error:[b]MessageExecuted as user: COMPANY\xxxxxxxxx. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.[/b]So, I've tried granting EXEC on sp_send_dbmail. I've tried sp_addrolemember to DatabaseMailUserRole. I've tried setting trustworthy ON.Basically, everything I've found online has been tried. It's driving me crazy... am I missing something simple?Thanks.Fri, 18 May 2012 12:19:23 GMTsickpup