March 13, 2009 at 4:31 pm
I am trying to send an email using sp_send_dbmail. I need to send rows from a table as an attachment. I wrote a stored proc, usp_StatementDistribute, that inserts the rows into a table. Then I call another stored proc, usp_BcpFileOut, that uses bcp to write the rows from the table to a file. usp_StatementDistribute stored proc then execs sp_send_dbmail. I coded and tested this while logged on as a member of the sysadmin role, and everything worked as expected. But when executed with ADO.NET from an ASP.NET 2.0 web page on our intranet, the stored proc is executed with a SQL Server user "DFT". The problem I am having is that the I get this message when executing the top-level stored proc:
Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
The 'usp_bcpFileOut' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
I googled for a while, and tried these steps:
1. xp_cmdshell is enabled. It runs under a domain account.
2. The sql server account "DFT" has permission to execute xp_cmdshell and sp_send_dbmail.
3. EXEC sp_xp_cmdshell_proxy_account 'mydomain\myuser', 'mypassword'; This is the domain account that runs xp_cmdshell. I can EXEC @RetVal = master..xp_cmdshell N'dir *.*' and it works fine.
And, to add insult to injury, I now get the same messages when executing while logged on as a member of the sysadmin role. To quote an old joke, I have right field so screwed up that no one can play it.
Any ideas what to do next?
March 14, 2009 at 5:41 pm
Why not just use sp_send_dbmail's functionality to send the query as an attachment?
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 16, 2009 at 5:33 pm
That's what I ended up doing. I still don't know what I changed to make the original one not work, but doing it this way make for cleaner code. Thanks for pointing me in the right direction.
March 16, 2009 at 5:41 pm
We were using xp_cmdshell to run DTS automation through Agent proxy so Microsoft changed it in 2005 so the only way your code would run is to create an admin account to run Asp.net which is not good security since you have other options.
Kind regards,
Gift Peddie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply