Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL 7 Trigger Permissions

    Hi All

    I have a trigger that runs after a record is added to a table. This trigger is owned by <font color=blue>dbo</font color=blue>. It uses the extended stored proceedure <font color=blue>xp_sendmail</font color=blue>.

    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 <font color=blue>xp_sendmail</font color=blue> 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?

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL 7 Trigger Permissions

    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!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL 7 Trigger Permissions

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •