Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

permissions to user to send email

Posted on 2011-09-16
10
Medium Priority
?
933 Views
Last Modified: 2012-05-12
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.SqlException: The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.The EXECUTE permission was denied on the object 'sp_OASetProperty', database 'mssqlsystemresource', schema 'sys'.The EXECUTE permission was denied on the object 'sp_OASetProperty', database...

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.
0
Comment
Question by:bamapie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 10

Expert Comment

by:gaurav05
ID: 36548095
Hi,

A user who already has sysadmin privileges must execute that script.
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 36548117
You say that the user rsuser1 exist, in which schema dbo or sys ?
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36549116
Please give execute permission to that user.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 3

Expert Comment

by:hspoulsen
ID: 36549176
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
 
0
 

Author Comment

by:bamapie
ID: 36550050
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:

 
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;
	END

Open in new window

0
 

Author Comment

by:bamapie
ID: 36905242
No idea on this one?  I'd love to figure this out.  It's still an issue.
0
 
LVL 3

Expert Comment

by:hspoulsen
ID: 36908370
I send mails this way:
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DatabaseMail',
@recipients = 'hsp@stovi.com', 
@body = 'lots of text', @subject = 'title/subject of mail'

Open in new window


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.sqlservercentral.com/Forums/Topic253575-149-1.aspx

Our SQL Server send mails every day.
0
 

Author Comment

by:bamapie
ID: 36989295
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.

0
 
LVL 3

Accepted Solution

by:
hspoulsen earned 2000 total points
ID: 36991717
was it on the SQL Server Agent -> properties -> Alert System  (check the checkbox) ?
0
 

Author Closing Comment

by:bamapie
ID: 37410884
I honestly don't remember now, but...cheers.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question