SQLServerCentral Article

Using Xp_cmdshell

,

Extended stored procedures allow DBAs to execute any non-interactive

DLL file from an ISQL prompt, or stored procedure. Any C program

compiled as a DLL file can be run as a user-defined procedure.

Extended stored procedures are some of the least utilized tools

that Microsoft provides a DBA.

The xp_cmdshell procedure allows the DBA to execute any operating

system command via TSQL. One can, for example notify users that the

SQL Server will be stopping by running the following command:

EXEC master..xp_cmdshell 'net send knight System will shutdown in 30 seconds', no_output

WAITFOR DELAY '00:00:30'

EXEC master..xp_cmdshell 'd:\mssql\exec\recycleserver.bat'

The no_output parameter after the procedure will make the results

of the net send command invisible to the ISQL window. The output that is not suppressed is displayed as a varchar (255) datafield.

Xp_cmdshell becomes more challenging when you want to make the command you wish to run

a variable. The following is an excerpt from sp_axeusers (for SQL Server 6.5):

 -- .... OMITTED DECLARE STATEMENTS ....
 
 -- CREATE TEMPORARY TABLE FOR SP_WHO TO POPULATE
create table #usrkill ( 
spid smallint,
status varchar(32),
loginame varchar(32),
hostname varchar(32),
blk char(8),
dbname varchar(32),
cmd varchar(255))
-- INSERT INTO TEMP TABLE
insert into #usrkill exec sp_who
-- PREPARING VARIABLES FOR NET SEND
SELECT @NOTIFYMSG1 = '"NET SEND '
SELECT @NOTIFYMSG2 = ' YOUR SESSION WILL DISCONNECT IN 30 SECONDS'
SELECT @NOTIFYMSG3 = '"'
-- BEGIN CURSOR
DECLARE CNOTIFYUSR SCROLL CURSOR FOR 
SELECT DISTINCT HOSTNAME from #usrkill where dbname = @USRDB
OPEN CNOTIFYUSR
FETCH FIRST FROM CNOTIFYUSR into @hostname
select @NOTIFYSTATE = @NOTIFYMSG1 + @hostname
+ @NOTIFYMSG2 + @NOTIFYMSG3
exec master..xp_cmdshell @NOTIFYSTATE, no_output
--... Omit rest of code 
--... Full code is at : ftp://downloads.swynk.com/sp_axeusers.SQL

As you can see above, to place strings inside a xp_cmdshell command, you will have to string

the variables together like @NOTIFYSTATE.

Before you begin implementation of this extended stored procedure into your

applications, you will need to be aware of the security issues. When executing xp_cmdshell, you will have the same rights and permissions

as whichever NT account is configured to start SQL Server (MSSQLSERVER service). This account is generally either

an administrator or system account. In either case, you pose a substantial security risk if you don't lock down the extended stored procedure to not allow your non-sa users to execute it.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating