SQL 7 Trigger Permissions

Topic

New Reply

Hi All

I have a trigger that runs after a record is added to a table. This trigger is owned by dbo. It uses the extended stored proceedure xp_sendmail.

I have granted Seelct and Insert permissions on the table to the user DatabaseUsers (from the DatabaseUsers Login).

My problem is that when a user who is a member of this login and consequently this database user executes the trigger by adding a record to the table, SQL Server reports that they don’t have permissions to run the xp_sendmail stored proceedure.

I thought that the trigger runs in the security context of it’s owner (which in this case is dbo), so the trigger should also have permission to use the xp_sendmail stored proceedure.

Can anyone spread any light on this?

Reply | Quote

Replies

Jayden:

Not sure why you’re experiencing that issue, but perhaps a work around would be to create a stored procedure owned by dbo with Execute permissions granted to your DatabaseUsers group that invokes xp_sendmail; call the stored procedure from the trigger. Good luck!

Reply | Quote

Hi Shane

Thanks for your reply. Unfortunately I no longer work at the place where I was doing this … so can’t test out your theory…but thanks anyway.

Jayden

Reply | Quote