Newbee question about permissions and sp_OACreate

Hi,
I'm creating a trigger that shall monitor a table on inserts and if certain
conditions are met, it should send a mail the administrator.
I do not want to use SQLmail for many reasons so I'm using the extended
procedure sp_OACreate to create an instance of the CDO.Message object
The problem is that only symins can use the extended procedure
sp_OACreate, and of cause I do not want users to be symins.
I think I read or learned somewhere that stored procedures runs under the
security context of the user that created the procedure, so why can't I as
the symin create a my_sendmail procedure that uses the sp_OACreate
extended procedure and grant permission to all users to the my_sendmail
procedure?
Or, is there another way?
Regards,
Jorgen D.First of all, this excedes the purpose of triggers. What has sending mail go
t
to do with ACID? It would be much more efficient if you created a job to
monitor the table and log these special events in a separate table/file and
mail the report/warning.
Second, why is it not possible for you to use SQL Mail? It's far more
efficient than (ab)using sp_OA procedures. And it can be used by any user
without compromising the security.
How about notification services? If mailing these messages was requested by
a manager, you can be pretty sure, he'll start hating it after about a month
.
Either the messages will be too frequent or too infrequent and he'll still
need to keep statistics somewhere, somehow. But I'm just guessing here...
Anyway, seems to me that what you really need is some sort of logical event
logging. This IS the purpose of triggers - propagating changes in one table
to another.
Be careful out there...
ML|||Why do some people answer a question with more questions? (no, thanks for
your input)
My trigger do populate data to other tables (sending work orders to PDA’s,
via merge replication), and when done it’s important that the users
implicated (the ones that gets the work order) gets an mail/sms to inform
them about it (they should react to it immediately)
This mail shall originate form different senders, therefore not SQL-mail
which is based on a single profile (perhaps there is a way around that that
I
didn’t find)
Another reason not to use SQL-mail is in another scenario, that I’m not
going to explain in detail, but the mail should be HTML formatted report
larger than 8000 bytes.
Notification services? Hmmm, I’ll look into that, (as I don’t know what
that
is)
But to get back to my original question….. If I want to use an extended
procedure in my own procedure, how can I set permissions so the ordinary
mortal users can execute that?
Regards,
Jorgen D.
"ML" wrote:

> First of all, this excedes the purpose of triggers. What has sending mail
got
> to do with ACID? It would be much more efficient if you created a job to
> monitor the table and log these special events in a separate table/file an
d
> mail the report/warning.
> Second, why is it not possible for you to use SQL Mail? It's far more
> efficient than (ab)using sp_OA procedures. And it can be used by any user
> without compromising the security.
> How about notification services? If mailing these messages was requested b
y
> a manager, you can be pretty sure, he'll start hating it after about a mon
th.
> Either the messages will be too frequent or too infrequent and he'll still
> need to keep statistics somewhere, somehow. But I'm just guessing here...
> Anyway, seems to me that what you really need is some sort of logical even
t
> logging. This IS the purpose of triggers - propagating changes in one tabl
e
> to another.
> Be careful out there...
>
> ML|||Any user who needs to execute sp_OA procedures must have appropriate
privileges to do so.
I'm sorry, I'm simply curious as to what you're trying to do. You say
certain users must be notified (with a large report, even) of certain change
s
to yor data. And this must be done immediately after those changes were
commited. Why? Do this users then do something? Couldn't that be done
automatically?
It's like in that nuclear plant: "push this button when that light goes on"
- why not just automatically do whatever the button does when conditions are
met which normally tur the light on...?
Instead of sending huge reports you could simply send a link to the report
built by the SQL Reporting Services.
Consider the alternatives before you compromise security.
ML|||Some users are security guards which have to respond to alarms. When they ge
t
notified they should turn on their PDA and connect to the server to get
detailed information of the alarm (address, codes etc.). Other users are
doing emergency road help. So the notification is important to be timely.
They can’t ask the server/system to drive to an incident.
About the reports – They aren’t huge. A single HTML formatted page (tabl
es,
and colours) is larger than 8K which is the limit of SQL-mail, further more
I
can’t get SQL-mail to send in HTML. The reports are requested from the use
rs
PDA to be sent to the customers email. The reports are dynamically created o
n
the server and can’t be sent from the PDA (they don’t have the data)
Conclusion: Since I have to use CDO.Message I have to grant all users public
access to the master database, and then on the sp_OAxxxxx procedures, give
the users EXEC rights. What happened to the ideology “The code of a stored
procedure runs within the security context of the creator”?
"JorgenD" wrote:
> Why do some people answer a question with more questions? (no, thanks for
> your input)
> My trigger do populate data to other tables (sending work orders to PDA’
s,
> via merge replication), and when done it’s important that the users
> implicated (the ones that gets the work order) gets an mail/sms to inform
> them about it (they should react to it immediately)
> This mail shall originate form different senders, therefore not SQL-mail
> which is based on a single profile (perhaps there is a way around that tha
t I
> didn’t find)
> Another reason not to use SQL-mail is in another scenario, that I’m not
> going to explain in detail, but the mail should be HTML formatted report
> larger than 8000 bytes.
> Notification services? Hmmm, I’ll look into that, (as I don’t know wha
t that
> is)
> But to get back to my original question….. If I want to use an extended
> procedure in my own procedure, how can I set permissions so the ordinary
> mortal users can execute that?
> Regards,
> Jorgen D.
>
> "ML" wrote:
>|||You're describing a messaging and reporting system for which web services ar
e
the ideal solution. You have a central data server or a data warehouse and
several clients, that aren't capable of maintaining a 24/7 connection to HQ.
Wouldn't it be more efficient if the clients would only receive short
messages, rather than full reports? Upon the notification they could then
connect to the Report server to see the details of the arising situation and
then decide if they need to download them for off-line use.
That way you can allow access to the same data to the PDA users and to the
desktop users.
You might end up developing a messaging and reporting system using
webservices and smart clients. You could even earn $50 grand!!! :)
(http://www.csdevcompetition.com/)
Giving master database/extended procedure privileges to the Public role is a
bad idea. It is IMHO essential to use a custom role for this - with
explicitly limited privileges.
Also consider the fact that your inserts might slow down due to the needed
complexity of these triggers - that is if the conditions are met frequently.
Of course you'll need a lot of error-handling as well - you don't want to
lock up the server, do you?
ML|||Hi
Look at SQL Server Notification Services to kick of a process where you have
a .NET application send the mail via SMTP.
If you kick off an sp_OA* within a trigger, and it fails, you end up having
a rolled back transaction, possible some e-mails being sent about data that
no longer exists.
If you do not want to use NS, then in your trigger, write a row to another
table indicate that something must be done, and then have SQL Server Agent
process poll the table and send the e-mail.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ML" wrote:

> You're describing a messaging and reporting system for which web services
are
> the ideal solution. You have a central data server or a data warehouse and
> several clients, that aren't capable of maintaining a 24/7 connection to H
Q.
> Wouldn't it be more efficient if the clients would only receive short
> messages, rather than full reports? Upon the notification they could then
> connect to the Report server to see the details of the arising situation a
nd
> then decide if they need to download them for off-line use.
> That way you can allow access to the same data to the PDA users and to the
> desktop users.
> You might end up developing a messaging and reporting system using
> webservices and smart clients. You could even earn $50 grand!!! :)
> (http://www.csdevcompetition.com/)
>
> Giving master database/extended procedure privileges to the Public role is
a
> bad idea. It is IMHO essential to use a custom role for this - with
> explicitly limited privileges.
> Also consider the fact that your inserts might slow down due to the needed
> complexity of these triggers - that is if the conditions are met frequentl
y.
> Of course you'll need a lot of error-handling as well - you don't want to
> lock up the server, do you?
>
> ML