permissions required for CDOSys stored procedures

[divinyl]

Hi there

I'm doing some analysis on the database applications in my organisation before migrating the databases to a new server. One of these makes use of CDOSys objects for sending mail, instead of SQL Mail. There are a number of stored procedures within the database that call the sp_OACreate & sp_OASetProperty. Apparently only members of the sysadmin role can execute these stored procedures, however, the sql login for this application is not a sysadmin! I thought perhaps there was a mistake in books online but i've looked on google and the permission requirements are the same - must be sysadmin. Any ideas how it still manages to function without these rights?

Thanks in advance!
Div

 

[ptheriault]

have you tried to grant that user exec permissions on the OA stored procedures? I believe there are 7 of them total.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[onpnt]

Just a side note I would not recommend granting execute on the OA proc's. You should control that execution elsewhere.

And out of curiosity why would you want your end users using your database server to send emails? Might be a good time to rethink that logic.

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[ptheriault]

what version of SQL Server is this for?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[divinyl]

hi All

Thanks for the responses. This is for SQL 2000. Maybe i wasn't clear - i'm not trying to get the application working, it already works. The thing im trying to figure out is HOW it manages to work even though the sql user is not a member of sysadmin?? Because according to Books Online this is not required. I'm pretty sure the stored procedures are being run in the context of the application user, which is not an sa. Perhaps this is just a mistake in Books Online?

Thanks

Div