Re: Best Practice - xp_cmdshell question

smaas_at_newsgroups.nospam
Date: 11/13/04 

Date: Fri, 12 Nov 2004 15:27:05 -0800

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: Notification when record deleted from SQL Server table?
    ... I expect writing a custom trigger would be a start but getting it to communicate back to a specific application would be a challenge--even for a CLR executable. ... Hitchhiker's Guide to Visual Studio and SQL Server ... so it can delete the corresponding file from the file system. ... Have a look at SQL Server Notification Services, or invoke a managed assembly from the delete trigger. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Trigger on a table in TempDB
    ... dropped everytime SQL Server restarts (due to the fact that tempdb gets ... "create trigger" statement within a stored procedure. ... a batch file using OSQL or SQLCMD and create the trigger per command line. ...
    (comp.databases.ms-sqlserver)
  • Re: Help for an Oracle Newbie
    ... application I wrote using SQL Server ported over to Oracle. ... Obviously there are several built in data types in SQL Server that do ... Of course I had to use a different name for the trigger under each ...
    (comp.databases.oracle.server)
  • Re: Records not inserting...
    ... > I am totally new to SQL server. ... However, in a trigger, would I be ... test the effects of certain data modifications and to set conditions for ... UPDATE statements. ...
    (microsoft.public.sqlserver.odbc)
  • Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !
    ... >into SQL server, the record exist anyway! ... >If you refresh the data you will see the record... ... it gets refreshed after trigger excecution automatically. ... I don't know your monitoring system, but I guess that removing the result ...
    (microsoft.public.sqlserver.programming)