SQL Server T-SQL User-Defined Function of the Week

Searching syscomments for Uses of xp_cmdshell

Volume 1 Number 13  February 11, 2003

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

The problems caused by the SQL Slammer Worm prompted a lot of 
sites to upgrade to SQL Server 2000 Service Pack 3 (SP3) in a 
hurry. That wasn't such a bad idea, but as the saying goes, 
"Haste makes waste."  One of my client's sites did this and a few 
key features of the application just stopped working.

The reason they stopped was a change in the way security works
when using a proxy account to run xp_cmdshell.  I've documented
the problem in a web article published by Database Journal. It 
goes into depth about the problem. Here's a link to the article:
http://www.databasejournal.com/features/mssql/article.php/1580041

Once I suspected that the problem was the inability to run 
xp_cmdshell, I wanted to find out quickly exactly where the 
application was using it. I turned to this week's UDF to find 
all the places that xp_cmdshell was mentioned.

udf_SQL_SearchDBObjectsTAB searches the text of all objects 
that store their definition in syscomments.  Here's the 
CREATE FUNCTION script:

/------------- Copy From Below this line ----------------------\
CREATE FUNCTION dbo.udf_SQL_SearchDBObjectsTAB (

    @SearchFor sysname = NULL -- String to search for
  , @Just4Type varchar(2) = NULL -- Object type to search
        -- NULL for all, or
        -- F  = Any function
        -- C  = CHECK constraint
        -- D  = Default or DEFAULT constraint
        -- FN = Scalar function
        -- IF = Inline table-function
        -- P  = Stored procedure
        -- TF = Table function
        -- TR = Trigger
        -- V  = View
 
) RETURNS TABLE
/*
* Searches the text of SQL objects for the string @SearchFor. 
* Returns the object type and name as a table.
*
* Common usage:
SELECT * from udf_SQL_SearchDBObjectsTAB('xp_cmdshell', NULL)
*
* © Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically. -- T-SQL UDF Vol 1 #13
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/

AS RETURN

SELECT TOP 100 PERCENT WITH TIES -- TOP clause makes Order by OK
       CASE xtype WHEN 'C'  THEN 'Check Constraint'
                  WHEN 'D'  THEN 'DEFAULT Constraint'
                  WHEN 'FN' THEN 'Function/Scalar'
                  WHEN 'IF' THEN 'Function/Inline'
                  WHEN 'P'  THEN 'Stored Procedure'
                  WHEN 'TF' THEN 'Function/Table'
                  WHEN 'TR' THEN 'Trigger'
                  WHEN 'V'  THEN 'View'
                  ELSE 'Unknown'
                  END as [Object Type]  
      , OBJECT_NAME(o.[id]) as [Name]
    FROM syscomments c
        INNER JOIN sysobjects o
            ON c.[id] = o.[id]
    WHERE (@Just4Type IS NULL 
             OR (o.xtype = @Just4Type 
                   AND o.status >= 0)
             OR (@Just4Type = 'F' -- F works for all functions
                   AND o.xtype in ('FN', 'TF', 'IF')
                   AND o.status >= 0)
           ) 
          AND [text] LIKE '%'+@SearchFor+'%'       
    GROUP BY xtype, o.[id]     
    ORDER BY CASE xtype WHEN 'C'  THEN 'Check Constraint'
                  WHEN 'D'  THEN 'DEFAULT Constraint'
                  WHEN 'FN' THEN 'Function/Scalar'
                  WHEN 'IF' THEN 'Function/Inline'
                  WHEN 'P'  THEN 'Stored Procedure'
                  WHEN 'TF' THEN 'Function/Table'
                  WHEN 'TR' THEN 'Trigger'
                  WHEN 'V'  THEN 'View'
                  ELSE 'Unknown'
                  END  
           , OBJECT_NAME(o.[id])


GO
GRANT SELECT on dbo.udf_SQL_SearchDBObjectsTAB to PUBLIC
GO
\------------ Stop copying above this line --------------------/

It's a pretty strait forward Inline UDF but there are a few 
tricks involved.  Let's touch base on those and they I'll show 
how I used it.

Sometimes I only want to search a particular object type such
as stored procedures or triggers.  The second parameter let's
the caller specify the object type to search.  The values come
from the xtype or type column in sysobjects.  However, I added
to special values.  The first is NULL, for all objects. The
second is F for all types of functions.  

The three types of UDFs have three different object types in 
sysobjects:
         FN for Scalar function
         IF for Inline table-function
         TF for Table function         
That makes it a little tricky to search them all.

An expressions in the WHERE clause implements the logic that 
I want for selecting the objects.

    WHERE (@Just4Type IS NULL 
             OR (o.xtype = @Just4Type 
                   AND o.status >= 0)
             OR (@Just4Type = 'F' -- F works for all functions
                   AND o.xtype in ('FN', 'TF', 'IF')
                   AND o.status >= 0)
           ) 

The first subexpression, "@Just4Type IS NULL", includes all
objects that have entries in syscomments when NULL is used as the
second parameter.  The second subexpression 
"o.xtype = @Just4Type" matches the object type for exact matches.
The third subexpression
               (@Just4Type = 'F' -- F works for all functions
                   AND o.xtype in ('FN', 'TF', 'IF')
handles F and chooses any of the function types.

The other crafty bit of SQL is the way the sorting is done.
Inline UDFs and views don't allow the use of the ORDER BY clause
unless there is a TOP clause in the SELECT.  To satisfy that 
requirement the "TOP 100 PERCENT WITH TIES" clause is used at
the beginning of the SELECT.  Since it doesn't eliminate any 
rows from the result, it can be used whatever you want to add
an ORDER BY clause to an Inline UDF or view.

Since it's now OK to use an ORDER BY clause, I've used an
expression to get the information sorted the way that I want
it instead of sorting by one of the fields in the result set:

    ORDER BY CASE xtype WHEN 'C'  THEN 'Check Constraint'
                        WHEN 'D'  THEN 'DEFAULT Constraint'
                        WHEN 'FN' THEN 'Function/Scalar'
                        WHEN 'IF' THEN 'Function/Inline'
                        WHEN 'P'  THEN 'Stored Procedure'
                        WHEN 'TF' THEN 'Function/Table'
                        WHEN 'TR' THEN 'Trigger'
                        WHEN 'V'  THEN 'View'
                        ELSE 'Unknown'
                        END  

In place of sorting by the object type codes, the expression
sorts by a matching name.  I've put the Functions together by 
manipulating their names so the sort together.  I find that more 
natural to read.

Now let's use the UDF to search for use of xp_cmdshell:

/------------- Copy From Below this line ----------------------\
SELECT * from udf_SQL_SearchDBObjectsTAB('xp_cmdshell', NULL)
GO
\------------ Stop copying above this line --------------------/
(Results)
Object Type        Name                                        
------------------ --------------------------------------------
Function/Inline    udf_SQL_SearchDBObjectsTAB
Stored Procedure   sp_textcopy
Stored Procedure   usp_SQL_InstanceList
(End of results)

Only three uses show up in the database that I use to develop
DBA tools.  That's not much. Not that using xp_cmdshell is really
advisable in the first place due to security concerns.  But 
sometimes there's no other way to get the job done from inside
the SQL server engine.

udf_SQL_SearchDBObjectsTAB was able to show me where my 
client had used xp_cmdshell.  Once the problem caused by 
installing SP3 was resolved, knowing where it was used let me 
go back and test that each of them worked.

+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+