Executing a reboot command from SQL

[Steve Duke]

Here is the block of the sproc that I?ve created. All lines execute
fine except for the ?exec master..xp_cmds hell @reboottc?. In order to
make this work from the query analyzer I had to set the MSSQLSERVER
service to have the same credentials as the thin clients we are trying
to shut down, that means the service is running as an Administrator on
the sql server with the same u/p as administrator on thin clients.
declare @RoomNumber varchar(6) -- for testing

set @RoomNumber = '1144' -- for testing

DECLARE @RoomIP varchar(15), @RebootTC
varchar(120), @shutdownStart varchar(20), @shutdownEnd varchar(85)

-- @ShutDownStart and @ShutDownEnd will be used
with the @RoomIP to build the shutdown command sent to the thin
clients

SET @ShutDownStart = 'shutdown -r -f -m \\'

SET @ShutDownEnd = ' -t 5 -c "Mandatory
iConnect Reboot issued during guest check in/out process"'

SELECT @RoomIP = IP FROM Rooms WHERE
RoomNumber = cast(@RoomNumbe r AS INT)

SET @RebootTC = @ShutDownStart + @RoomIP +
@ShutDownEnd

-- print @RebootTC -- for testing

-- @RebootTC ends up looking like the line below based on the
@RoomNumber

-- shutdown -r -f -m \\172.18.16.103 -t 5 -c "Mandatory iConnect
Reboot issued during guest check in/out process?

-- Also the command below runs correctly when executed from SQL Query
Analyzer
EXEC master..xp_cmds hell @RebootTC

[Simon Hayes]

"Steve Duke" <st*******@Comc ast.net> wrote in message
news:d3******** *************** ***@posting.goo gle.com...

Here is the block of the sproc that I?ve created. All lines execute
fine except for the ?exec master..xp_cmds hell @reboottc?. In order to
make this work from the query analyzer I had to set the MSSQLSERVER
service to have the same credentials as the thin clients we are trying
to shut down, that means the service is running as an Administrator on
the sql server with the same u/p as administrator on thin clients.

<snip>

I admit that I haven't looked at your code in detail, but all code executes
on the server, so any xp_cmdshell commands execute as the server, not the
client. That means that if you want a command to shut down servers or
workstations, then you'll have to give those permissions to the MSSQL or SQL
Server Agent proxy service account (see "xp_cmdshel l" in Books Online). You
might want to look at doing this outside MSSQL, unless you have a compelling
reason to do it in the database.

Simon