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.
Related content 2019-03-25 38,883 reads EXECute SQL Commands over entire sets of parameters!A super-powered EXEC on steroids, the Power Tool every DBA wants for Christmas. 2010-03-16 (first published: 2010-03-04) 2,899 reads An Automated Solution for Migrating Database StructuresThis article by Simon Galbraith (from Red Gate software, maker of SQL Compare) discusses migrating changes from development to staging, QA, and on to production. If you've never seen the need for a schema compare tool (Steve Jones!), this is worth reading. 2003-05-06 2,750 reads  Logins, Users, and Roles - Getting StartedDo you know the difference between a login and a user? What's the best way to add them; Enterprise Manager, T-SQL, or SQL-DMO? In this beginner level article Andy demonstrates how to use all three methods to add logins and users and offers his view of which is the best technique. 2005-09-30 (first published: 2003-02-19) 37,455 reads Nightly Failed Jobs ReportDo you get tired of reviewing each SQL Agent notifications nightly to determine which SQL Agent jobs failed? Is there a significant number of SQL Agent job notifications that it takes a while to review each? Here is an alternative that allows you to have a single email report of all job failures. 2002-09-17 11,724 reads |