Best Practice - xp_cmdshell question

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?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?|||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 pull
s
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 associate
d
> 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..
.
> are
>
>|||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...[vbcol=seagreen]
> 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:
>|||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_cmdshe
ll
> without direct user execute permissions by changing your user database own
er
> 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 onl
y
> sysadmin role members have permissions to create dbo-owned objects in that
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||Why not have the trigger insert the data into a table and then have a
scheduled job routinely poll the table for items to be printed. You can then
use vbscript or xp_cmdshell in the job to do the printing.
Andrew J. Kelly SQL MVP
"smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com>
wrote in message news:1BDEB22E-0923-4825-AF22-62037C376AEB@.microsoft.com...[vbcol=seagreen]
> 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:
>|||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:
>
>|||I thought this might be a good idea, but the tag reading software is
currently scheduled to run every *second* and so any scheduled job to
retrieve data would need to run just as frequently. From what I can see, th
e
minimum interval for a scheduled job is 1 minute. If so, this would not
work, because users cannot be asked to wait for 1+ minutes before a tag
prints. We may just go ahead and stick with what we have as an acceptable
risk. The only saving grace is that the account is an application account -
users do not know the password and thus it is already as if the execution wa
s
running under a service account of sorts...
"Andrew J. Kelly" wrote:

> Why not have the trigger insert the data into a table and then have a
> scheduled job routinely poll the table for items to be printed. You can th
en
> use vbscript or xp_cmdshell in the job to do the printing.
> --
> Andrew J. Kelly SQL MVP
>
> "smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com
> wrote in message news:1BDEB22E-0923-4825-AF22-62037C376AEB@.microsoft.com..
.
>
>|||You can run a job that basically never ends by placing it in a while loop
such as:
WHILE 1 = 1
BEGIN
** Do your stuff here
WAITFOR DELAY '00:00:05'
END
You can then print every 5 seconds (or whatever) if there is anything to
process. But stuff such as printing is usually best served in the
application and not directly from the database.
Andrew J. Kelly SQL MVP
"smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com>
wrote in message news:1D71CE15-0F71-4219-B162-E6566C396CA7@.microsoft.com...[vbcol=seagreen]
>I thought this might be a good idea, but the tag reading software is
> currently scheduled to run every *second* and so any scheduled job to
> retrieve data would need to run just as frequently. From what I can see,
> the
> minimum interval for a scheduled job is 1 minute. If so, this would not
> work, because users cannot be asked to wait for 1+ minutes before a tag
> prints. We may just go ahead and stick with what we have as an acceptable
> risk. The only saving grace is that the account is an application
> account -
> users do not know the password and thus it is already as if the execution
> was
> running under a service account of sorts...
> "Andrew J. Kelly" wrote:
>