Re: Best Practice - xp_cmdshell question

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 11/16/04 

Date: Tue, 16 Nov 2004 07:57:53 -0600

As Mike and Andrew suggested, the best approach is to remove the xp_cmdshell
execute from the trigger and use an alternate method. Of course, this
assumes you can modify 3rd party trigger.

> The table is not owned by dbo - it is owned by an application account, and
> the ownership has to remain, or the application will not function
> properly.
> I'm still looking for an alternative way to write the file without using
> xp_cmdshell...

The ownership chain technique I mentioned will only work for dbo-owned
objects in an sa-owned database. You could create a dbo-owned proc that
executes the needed xp_cmdshell command and execute that instead so that
users need only permissions on the procedure. However, since this will
require changing the trigger anyway, you might as well 'do it right' using a
different technique. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"smaas@newsgroups.nospam" <smaasnewsgroupsnospam@discussions.microsoft.com> 
wrote in message news:DF0377B7-8DDA-4E19-AEB3-E7D2C9EA10D4@microsoft.com...
> Dan,
>
> The table is not owned by dbo - it is owned by an application account, and
> the ownership has to remain, or the application will not function 
> properly.
> I'm still looking for an alternative way to write the file without using
> xp_cmdshell...
>
> "Dan Guzman" wrote:
>
>> If the table is owned by 'dbo', you can allow the trigger to use 
>> xp_cmdshell
>> without direct user execute permissions by changing your user database 
>> owner
>> to 'sa' (using sp_changedbowner).  With SQL 2000 SP3, you'll also need to
>> enable the 'db chaining' (a.k.a. cross-database chaining) option un your
>> user database.  This will provide an unbroken ownership chain between 
>> your
>> trigger and the xp_cmdshell extended stored procedure.
>>
>> Note that you should enable 'db chaining' in an sa-owned database when 
>> only
>> sysadmin role members have permissions to create dbo-owned objects in 
>> that
>> database.
>>
>> -- 
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>

Relevant Pages

  • Re: Best Practice - xp_cmdshell question
    ... If the table is owned by 'dbo', you can allow the trigger to use xp_cmdshell ... without direct user execute permissions by changing your user database owner ... SQL Server MVP ... >>> execute permissions on xp_cmdshell to non-sysadmin users. ...
    (microsoft.public.sqlserver.security)
  • Re: Executing a Views from a Trigger
    ... You don't execute queries inside views. ... Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL ... I search for exemple of trigger ...
    (comp.databases.ms-sqlserver)
  • Re: Event Handlers on lists
    ... Regards ... I would like to trigger some code to execute when an item ... > your database which run a script, which execute your code. ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Delete Trigger
    ... database whenever a row is deleted - of the form ... CREATE TRIGGER t_Delete ON tblOrderDetails AFTER DELETE ... Triggers execute once per statement, ...
    (comp.databases.ms-sqlserver)
  • Trigger: To fill another Database with using Stored Procedures of the other Database
    ... I fill from Database A with triggers Database B, ... add additional information accordingly, this Stored Procedures is ... trigger does not work anymore, even if I do a try catch over the whole ... that is taken to execute the trigger is ...
    (comp.databases.ms-sqlserver)