stored procedures, security, xp_cmdshell

[Bob Weaver]

To try to secure an outside web application we set up a user that
only has permission to execute a series of stored procedures that are
related to the appliation. Unfortunately a couple of those stored
procedures have to access system resources outside SQL Server so we
are using a call to xp_cmdshell from inside the stored procedure

SQL Server apparently won't let us do that unless we give our
restricted user (who is calling the initial stored procedure) execute
permission on xp_cmdshell. This, of course, negates most of the benefit
of setting up a restricted user. Is there some simple way I am missing
of running xp_cmdshell from inside s stored procedure without the user
calling the stored procedure having execute permission on xp_cmdshell?

[Jim W]

You have a couple of options that I can see...

write an extended stored procedure (a c program) instead if using xp_cmdshell.

a possibility, but I'm not sure if it will work: initiate a new sql process from the stored procedure, under a userid that does have xp_cmdshell permission.

Setuser won't work, unfortunately, because only the dbowner or the sysadmin can use it.

Your best bet will probably be to write an extended stored procedure though, and give the user rights to run it. There are a number of examples in the books online on that.

------------
Bob Weaver at 9/27/00 2:39:26 PM

To try to secure an outside web application we set up a user that
only has permission to execute a series of stored procedures that are
related to the appliation. Unfortunately a couple of those stored
procedures have to access system resources outside SQL Server so we
are using a call to xp_cmdshell from inside the stored procedure

SQL Server apparently won't let us do that unless we give our
restricted user (who is calling the initial stored procedure) execute
permission on xp_cmdshell. This, of course, negates most of the benefit
of setting up a restricted user. Is there some simple way I am missing
of running xp_cmdshell from inside s stored procedure without the user
calling the stored procedure having execute permission on xp_cmdshell?