February 3, 2010 at 4:16 am
Hey guys,
I hope someone can hep me out here, regarding DB mail in sql server 2005.
I am facing a problem where I am not able to send a custom mail using the sp_send_mail proc by giving it the parameters because its executing as user 'sa'.
As a sample I tried using it like to change the user context as given below.
CREATE procedure dbo.a_send_mail
WITH EXECUTE AS 'domain\windows_user'
AS
exec msdb..sp_send_dbmail
@profile_name = 'test_profile',
@recipients = 'abc@email.in',
@subject = 'Test mail',
@body = 'Additional text'
-- Just to add, the user is a domain account and also runs the sql server and the agent services.
But when I check the mail
select top 50 * from.msdb.dbo.sysmail_faileditems order by 1 desc
I see that it still has been rejected as spam by server, probably because the last_mod_user column in the table above still 'sa'.
Any help would be appreciated!!
Regards
Akhil
February 3, 2010 at 4:24 am
... I see that it still has been rejected as spam by server ...
That is a mail server issue .... contact your mail admins.
They may help you figure out why it is marked as spam (they can provide the criteria that caused it to be marked spam)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 4, 2010 at 12:24 am
Thanks for your reply.
I am aware that this is a spam issue. But I am a little confused because of the below situation.
I am able to get notifications from jobs and alerts that are triggered through sql server agent.
However, when I use the send mail sp it doesn't work. The notifications seem to be directly sent out without being relayed through the mail server. I am able to see them in the sentitems table, but the mails sent through sp end up in faileditems.
It also gives a message that the mailbox is unavailable (but I know it is). Unable to relay the mail to abc@email.com
Do you have any diea about this?
Regards
Akhil
February 4, 2010 at 1:00 am
Did your mailadmins enable SMTP for your sqlserver ?
AFAIK a "regular" user just needs to be granted use of a sqlserver mail profile to be able to send mails.
So you can actually use multiple mailboxes with sqlserver and have users use different mail profiles to use their assigned mailbox.
Some help refs:
http://www.sql-server-performance.com/da_email_functionality.asp
http://www.mssqltips.com/tip.asp?tip=1736
Troubleshooting stuff:
use msdb
go
exec sysmail_help_queue_sp -- @queue_type = 'Mail'
go
select *
-- delete
from sysmail_event_log
order by log_date desc
select *
from sysmail_mailattachments
select *
from sysmail_allitems
order by sent_date
/* show failed stuff */
SELECT SEL.event_type
, SEL.log_date
, SEL.description
, SF.mailitem_id
, SF.recipients
, SF.copy_recipients
, SF.blind_copy_recipients
, SF.subject
, SF.body
, SF.sent_status
, SF.sent_date
FROM dbo.sysmail_faileditems AS SF
JOIN dbo.sysmail_event_log AS SEL
ON SF.mailitem_id = SEL.mailitem_id
/*
********** cleanup old messages **********
Declare @sent_before datetime
Select @sent_before = dateadd(MM,1,dateadd(dd,datediff(dd,0,getdate()),0))
print convert(varchar(26),@sent_before,121)
exec sysmail_delete_mailitems_sp @sent_before = @sent_before
, [ @sent_status = ] 'sent_status' ]
delete from sysmail_event_log where log_date < @sent_before
*/
/*
SELECT [account_id]
,[name]
,[description]
,[email_address]
,[display_name]
,[replyto_address]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_account]
SELECT [attachment_id]
,[mailitem_id]
,[filename]
,[filesize]
,[attachment]
,[last_mod_date]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_attachments]
SELECT [transfer_id]
,[uid]
,[filename]
,[filesize]
,[attachment]
,[create_date]
FROM [msdb].[dbo].[sysmail_attachments_transfer]
SELECT [mailitem_id]
,[profile_id]
,[recipients]
,[copy_recipients]
,[blind_copy_recipients]
,[subject]
,[body]
,[body_format]
,[importance]
,[sensitivity]
,[file_attachments]
,[attachment_encoding]
,[query]
,[execute_query_database]
,[attach_query_result_as_file]
,[query_result_header]
,[query_result_width]
,[query_result_separator]
,[exclude_query_output]
,[append_query_error]
,[send_request_date]
,[send_request_user]
,[sent_account_id]
,[sent_status]
,[sent_date]
,[last_mod_date]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_mailitems]
SELECT [profile_id]
,[principal_sid]
,[is_default]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_principalprofile]
SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile]
SELECT [profile_id]
,[account_id]
,[sequence_number]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profileaccount]
SELECT [uid]
,[text_data]
,[create_date]
FROM [msdb].[dbo].[sysmail_query_transfer]
SELECT [conversation_handle]
,[mailitem_id]
,[send_attempts]
,[last_send_attempt_date]
FROM [msdb].[dbo].[sysmail_send_retries]
SELECT [account_id]
,[servertype]
,[servername]
,[port]
,[username]
,[credential_id]
,[use_default_credentials]
,[enable_ssl]
,[flags]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_server]
SELECT [servertype]
,[is_incoming]
,[is_outgoing]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_servertype]
*/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 4, 2010 at 1:37 am
Here's a little test I performed:
I am logged in as dba_sysadmin, but in the script I use execute as login to change user context.
Test it;
Create login RegularSQLUser with password='sUPERSecr@'
go
USE [msdb]
GO
CREATE USER [RegularSQLUser] FOR LOGIN [RegularSQLUser]
GO
EXEC sp_addrolemember N'DatabaseMailUserRole', N'RegularSQLUser'
GO
--- Use the sysmail_add_account procedure as follows to create a Database Mail account,
-- using mail.dynanet.com as the mail server
-- and dinesh@dynanet.com as the e-mail account:
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Acc_SMTP_RegularUser',
@description = 'RegularUser dbmail',
@email_address = 'mymailaddress@here.com',
@display_name = 'RegularUser dbmail via mailbox',
@mailserver_name = 'relaysmtp.net', @use_default_credentials=0 -- use the credentials of the SQL Server Database Engine.
--@mailserver_type sysname = N'SMTP',
--@port int = 25,
--@replyto_address nvarchar(128) = NULL,
--@username nvarchar(128) = NULL,
--@password nvarchar(128) = NULL,
--@enable_ssl bit = 0,
-- Use the sysmail_add_profile procedure to create a Database Mail profile called Dinesh Mail Profile:
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name=N'prof_SMTP_RegularUser',
@description=N'dbmail SMTP_RegularUser'
-- User the sysmail_add_profileaccount procedure to add the Database Mail account
-- and Database Mail profile you created in previous steps.
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name=N'prof_SMTP_RegularUser'
, @account_name=N'Acc_SMTP_RegularUser'
, @sequence_number=1
-- Use the sysmail_add_principalprofile procedure
-- to grant the Database Mail profile access to the msdb public database role
-- and to make the profile the default Database Mail profile:
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'prof_SMTP_RegularUser',
@principal_name = 'RegularSQLUser',
@is_default = 0 ;
-- After configuring Database Mail, it should be tested using Database Mail's testing functionality.
-- Select the Send Test E-Mail option.
-- You can select the profile to test and send the mail and then verify.
go
/* now execute it as if you are logged in as 'regularsqluser' */
Execute as login='RegularSQLUser'
Select suser_name()
-- To send mail, you must be a member of the DatabaseMailUserRole in the msdb database,
-- and have access to at least one Database Mail profile.
Declare @body varchar(max)
Select @body = 'Use new feature of Database mail. No MAPI client needed [' + @@servername + '] - ' + suser_sname()
EXEC msdb.dbo.sp_send_dbmail
-- @profile_name = 'SMTP_DBA',
@recipients = 'myselfmailaddress@here.com',
@body = @body,
@subject = 'New Feature of Database Mail arcelormittal';
go
Declare @body varchar(max)
Select @body = 'Use new feature of Database mail. No MAPI client needed [' + @@servername + '] - ' + suser_sname()
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'prof_SMTP_RegularUser',
@recipients = 'myselfmailaddress@here.com',
@body = @body,
@subject = 'New Feature of Database Mail arcelormittal prof_SMTP_RegularUser';
go
revert;
go
/* cleanup */
use msdb
drop USER [RegularSQLUser];
drop login RegularSQLUser ;
go
use msdb
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'prof_SMTP_RegularUser';
EXEC msdb.dbo.sysmail_delete_profileaccount_sp @profile_name=N'prof_SMTP_RegularUser';
EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name=N'prof_SMTP_RegularUser';
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'Acc_SMTP_RegularUser';
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 5, 2010 at 9:48 am
SQLbee (2/3/2010)
Hey guys,I hope someone can hep me out here, regarding DB mail in sql server 2005.
I am facing a problem where I am not able to send a custom mail using the sp_send_mail proc by giving it the parameters because its executing as user 'sa'.
As a sample I tried using it like to change the user context as given below.
CREATE procedure dbo.a_send_mail
WITH EXECUTE AS 'domain\windows_user'
AS
exec msdb..sp_send_dbmail
@profile_name = 'test_profile',
@recipients = 'abc@email.in',
@subject = 'Test mail',
@body = 'Additional text'
-- Just to add, the user is a domain account and also runs the sql server and the agent services.
But when I check the mail
select top 50 * from.msdb.dbo.sysmail_faileditems order by 1 desc
I see that it still has been rejected as spam by server, probably because the last_mod_user column in the table above still 'sa'.
Any help would be appreciated!!
Regards
Akhil
Hello,
can you post the output of:
USE msdb
select * from sysmail_configuration
HTH,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
February 9, 2010 at 4:17 am
Here is the output of the query for configuration.
AccountRetryAttempts 1 Number of retry attempts for a mail server 2005-10-14 01:55:28.193 sa
AccountRetryDelay60 Delay between each retry attempt to mail server 2005-10-14 01:55:28.193 sa
DatabaseMailExeMinimumLifeTime600Minimum process lifetime in seconds 2005-10-14 01:55:28.193 sa
DefaultAttachmentEncodingMIME Default attachment encoding 2005-10-14 01:55:28.177 sa
LoggingLevel2Database Mail logging level: normal - 1, extended - 2 (default), verbose - 32005-10-14 01:55:28.193sa
MaxFileSize1000000Default maximum file size2005-10-14 01:55:28.193sa
ProhibitedExtensionsexe,dll,vbs,jsExtensions not allowed in outgoing mails2005-10-14 01:55:28.193sa
Something else interesting I found.
There is a public profile being used to send the mails. I can receive alerts that are generated as a result of DDL triggers or other triggers. However, I am not able to send test mails using the same profile. I suspected that it could be my credentials that the SMTP server might be rejecting, so I created a job and scheduled it to fire an sp for sending mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'test_profile',
@recipients = 'myemail@domain.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Test mail' ;
I believe that this should be sending the mail using the same credentials as the sql server service, but I still get the same message as below. I belong to the sysadmin role, so should be having access to send mails anyway , but still get the spam error message.
Just to add the mail mechanism works on the server without any proxies or credentials (there are none on the server), but despite me being a sysadmin, it doesn't seem to work. I created a job and with the sql server account as the owner for the above code, but it still failed.
Regardsx
Akhil
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply