bamapie
asked on
permissions to user to send email
I have a stored procedure that sends email successfully when I'm logged in as sa.
But from one of my apps--with different credentials--I get an error about:
System.Data.SqlClient.SqlE
Fine.
So I try to start doing what every link on the web--which you're about to post in as your answer--says to do. Like this:
grant exec on sp_OACreate to [rsuser1];
GO
But that yields:
"Cannot find the user 'rsuser1', because it does not exist or you do not have permission."
The user does exist. It's a SQL account, not a domain account, but it does exist in SQL.
What do I need to do? I really need some specifics.
Thanks.
But from one of my apps--with different credentials--I get an error about:
System.Data.SqlClient.SqlE
Fine.
So I try to start doing what every link on the web--which you're about to post in as your answer--says to do. Like this:
grant exec on sp_OACreate to [rsuser1];
GO
But that yields:
"Cannot find the user 'rsuser1', because it does not exist or you do not have permission."
The user does exist. It's a SQL account, not a domain account, but it does exist in SQL.
What do I need to do? I really need some specifics.
Thanks.
You say that the user rsuser1 exist, in which schema dbo or sys ?
Please give execute permission to that user.
it looks as if you are not using Database mail.
If you are on SQL Server 2005 or above, you should try that instead. Dbmail is a stable solution, whereas DCOM may cause problems from time to time.
We use database mail, and it works fine. The worst part in setting it up, is that you need access to a SMTP server. I had to have our SQL Server added to the exchange servers list of valid addresses.
Best regards,
Henrik Staun Poulsen
Stovi Software
If you are on SQL Server 2005 or above, you should try that instead. Dbmail is a stable solution, whereas DCOM may cause problems from time to time.
We use database mail, and it works fine. The worst part in setting it up, is that you need access to a SMTP server. I had to have our SQL Server added to the exchange servers list of valid addresses.
Best regards,
Henrik Staun Poulsen
Stovi Software
ASKER
gaurav05: I executed that script as sa.
samo4fun: This is one of the server's logins. Not sure what you mean.
Patel: In my original question, I include an error message that I received after attempting to assign EXECUTE permissions to my user.
hspoulsen: I am using SMTP. My entire procedure looks like what you see below:
samo4fun: This is one of the server's logins. Not sure what you mean.
Patel: In my original question, I include an error message that I received after attempting to assign EXECUTE permissions to my user.
hspoulsen: I am using SMTP. My entire procedure looks like what you see below:
ALTER PROCEDURE [dbo].[spSendSMTPMail]
@FromEmailAddress VARCHAR(32),
@ToEmailAddress VARCHAR(128),
@Subject VARCHAR(256),
@Body VARCHAR(8000)
AS
DECLARE @Message INT;
DECLARE @HR INT;
DECLARE @SRC VARCHAR(255);
DECLARE @DESC varchar(255);
EXEC @HR = sp_OACreate 'CDO.Message', @Message OUT;
EXEC @HR = sp_OASetProperty @Message, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2'
EXEC @HR = sp_OASetProperty @Message, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp2.flatsville.com'
EXEC @HR = sp_OASetProperty @Message, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', '25'
EXEC @HR = sp_OASetProperty @Message, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', '1'
EXEC @HR = sp_OAMethod @Message, 'Configuration.Fields.Update', NULL;
EXEC @HR = sp_OASetProperty @Message, 'To', @ToEmailAddress;
EXEC @HR = sp_OASetProperty @Message, 'From', @FromEmailAddress;
EXEC @HR = sp_OASetProperty @Message, 'Subject', @Subject;
EXEC @HR = sp_OASetProperty @Message, 'TextBody', @Body;
EXEC sp_OAMethod @Message, 'Send', NULL;
EXEC @HR = sp_OADestroy @Message;
-- Errorhandler
IF (@HR <> 0)
BEGIN
EXEC sp_OAGetErrorInfo @Message, @SRC OUT, @DESC OUT
SELECT HR = CONVERT(varbinary(4),@HR), Source=@SRC, Description=@DESC;
ENDASKER
No idea on this one? I'd love to figure this out. It's still an issue.
I send mails this way:
The worst problem was to get the database server recognised as a valid email sender with our exchange server team.
Another problem that I've had has been described here: http://www.sqlservercentra
Our SQL Server send mails every day.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DatabaseMail',
@recipients = 'hsp@stovi.com',
@body = 'lots of text', @subject = 'title/subject of mail'The worst problem was to get the database server recognised as a valid email sender with our exchange server team.
Another problem that I've had has been described here: http://www.sqlservercentra
Our SQL Server send mails every day.
ASKER
Okay, I fixed this by going somewhere and adding the "send Database Mail" permission to my user accounts in question.
Problem is, I was going to post that here, but now I can't figure out where I did that.
It was a GUI that looked like the "securables" screen. There it was, plain as day, a permission that I turned on for my user, and bam, it started working and continues to work.
Do you know what I'm talking about? I cannot find it for the life of me now.
Problem is, I was going to post that here, but now I can't figure out where I did that.
It was a GUI that looked like the "securables" screen. There it was, plain as day, a permission that I turned on for my user, and bam, it started working and continues to work.
Do you know what I'm talking about? I cannot find it for the life of me now.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I honestly don't remember now, but...cheers.
A user who already has sysadmin privileges must execute that script.