Trigger Creation, Permission, and Execution  
Author Message
LaNina





PostPosted: Wed Jul 13 22:06:01 CDT 2005 Top

SQL Server Developer >> Trigger Creation, Permission, and Execution

Hi all,

Here is the situation. I am trying to kick off an executable using
xp_cmdshell through a trigger. I need to kick of the particular .exe which
then raises an event to another system. However, I do not want to give the
privileges to the user that will be making the inserts to execut xp_cmdshell.
Is there any way to avoid this problem? I was hoping there was some way to
say for instance that if an admin creates the trigger it can execute for all
inserts regardless of the permission of the users? I also thought about
somehow invoking sp_start_job but that would require also giving more
permissions to the user than I want to. Any ideas for this would be
great.... The xp_cmdshell string is a constant and does not get built nor
does it change. I also do not care what it returns back.

Thanks!

SQL Server139  
 
 
Louis





PostPosted: Wed Jul 13 22:06:01 CDT 2005 Top

SQL Server Developer >> Trigger Creation, Permission, and Execution Nope, you cannot turn on ownership chaining for the master database, so this
is not possible.

As a suggesting, I would just build a queue table of commands and just fill
this table in and then run a job every minute to do this for you. Much safer
and less prone to failure that affects the user.

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP




> Hi all,
>
> Here is the situation. I am trying to kick off an executable using
> xp_cmdshell through a trigger. I need to kick of the particular .exe
> which
> then raises an event to another system. However, I do not want to give
> the
> privileges to the user that will be making the inserts to execut
> xp_cmdshell.
> Is there any way to avoid this problem? I was hoping there was some way
> to
> say for instance that if an admin creates the trigger it can execute for
> all
> inserts regardless of the permission of the users? I also thought about
> somehow invoking sp_start_job but that would require also giving more
> permissions to the user than I want to. Any ideas for this would be
> great.... The xp_cmdshell string is a constant and does not get built nor
> does it change. I also do not care what it returns back.
>
> Thanks!
>
>