Using Xp_cmdshell

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:

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.

By Brian Knight, 2001/05/03 Total article views: 35009 | Views in the last 30 days: 180 Your response  Rate this |   Join the discussion |  Briefcase		Related tags Administering     Stored Procedures     SQL Server 6.5     T-SQL       Related content Using XP_FILEEXIST By Brian Knight | Category: Stored Procedures | 10,424 reads Using Stored Procedures in SQL Server By Additional Articles | Category: Stored Procedures  Rate this | 2,995 reads Nightly Failed Jobs Report By Gregory Larsen | Category: Administering | 8,184 reads Does Your SP Choke on Errors? By Robert Marda | Category: Stored Procedures | 6,916 reads
Like this? Try these... Using xp_cmdshell By Haidong Ji | Category: Administering | 15,051 reads Rename Foreign Key Constraints By Robert Schmidt | Category: Miscellaneous | 161 reads Identify Primary Key Violation Prior to Insert (Updated) By kgayda | Category: Miscellaneous | 370 reads