Re: Best Practice - xp_cmdshell question

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

Date: Fri, 12 Nov 2004 19:24:20 -0600

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
"smaas@newsgroups.nospam" <smaasnewsgroupsnospam@discussions.microsoft.com> 
wrote in message news:1BDEB22E-0923-4825-AF22-62037C376AEB@microsoft.com...
> Mike,
>
> Thanks for answering.  I read the overview of Notification Services, and 
> I'm
> not seeing how it could help us resolve this particular issue, but perhaps 
> I
> need to clarify a bit.
>
> The table the trigger runs on is a "tag print" table.  Whenever users
> receive product, they use an application that writes to this table, 
> "tagging"
> the product with a tag code and also storing relevant information such as
> item #, etc.  From there, these tags need to be printed, so the trigger 
> pulls
> together the information from the inserted record plus formatting for the
> barcode program we use, and it then uses xp_cmdshell to run a VB 
> executable
> that creates a file with a title that looks to be a combination of date 
> and
> time and the contents of the file are those assembled in the trigger.
> Looking at the files created, I'm not sure why we really need the VB 
> program
> if we can have SQL Server write this file itself in some way that would 
> not
> need to use xp_cmdshell.  Do you have any suggestions?
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> The only way is using xp_cmdshell, but having a trigger (and its 
>> associated
>> locks) wait on a batch file is really suicide. If the batch fails, the
>> trigger rolls back the data.
>>
>> Notification Services might be a possible way to get this out of your
>> trigger. http://www.microsoft.com/sql/ns/default.asp
>>
>> Slap the 3rd party for their terrible understanding of what SQL Server's
>> function is.
>>
>> Regards
>> --------------------------------
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>>
>> IM: mike@epprecht.net
>>
>> MVP Program: http://www.microsoft.com/mvp
>>
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "smaas@newsgroups.nospam" 
>> <smaasnewsgroupsnospam@discussions.microsoft.com>
>> wrote in message 
>> news:467505F1-3DC5-4D0B-ACE4-B0C80EDD8F7B@microsoft.com...
>> > I would like to follow the Microsoft suggested best practice of 
>> > removing
>> > execute permissions on xp_cmdshell to non-sysadmin users.  Currently, 
>> > we
>> are
>> > allowing an account to run xp_cmdshell due to a trigger written by a 
>> > 3rd
>> > party that executes a batch file.  Are there any alternative ways of
>> > executing the batch file from the trigger that do not require 
>> > xp_cmdshell?
>>
>>
>>

Relevant Pages

  • Re: Stored procedure/trigger and scripts
    ... Can you run the vbs by calling it directly from SQL Server using xp_cmdshell (without a trigger)? ... If it does work this way I would probably create a job within SQL Server that would call the vbs at a scheduled interval. ... > SQL Server has permissions to execute xp_cmdshell. ...
    (microsoft.public.sqlserver.programming)
  • [NEWS] Multiple Vulnerabilities in Oracle Database (Trigger, Extproc, Wrapped Procedures, PL/SQL Inj
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... Multiple vulnerabilities were discovered in the Oracle database server. ... Oracle Trigger Abuse ... written in PL/SQL and execute with the privileges of the definer/owner. ...
    (Securiteam)
  • Re: Executing a dotnet console application from SQL Server 2005/2008
    ... Is the console application perhaps accessing data modified by the statement that fired the trigger? ... Keep in mind that the application is executing in a different session than the trigger and the modified data is still uncommitted. ... application that I want it to execute in a SQL trigger. ... I try to execute the code, the sql server management studio stays "running ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure/trigger and scripts
    ... must have been the trigger that locked up the table. ... SQL Server has permissions to execute xp_cmdshell. ... >> client to change their password they have to call the "Client Relations" ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure/trigger and scripts
    ... I just ran some additional tests and it looks like my trigger is fine. ... SQL Server has permissions to execute xp_cmdshell. ... >> client to change their password they have to call the "Client Relations" ...
    (microsoft.public.sqlserver.programming)