Finding Caller of SQL Function

Question

There's a SQL function that I'd like to remove from a SQL Server 2005 database, but first I'd like to make sure that there's no one calling it. I've used the "View Dependencies" feature to remove any reference to it from the database. However, there may be web applications or SSIS packages using it.

My idea was to have the function insert a record in an audit table every time it was called. However, this will be of limited value unless I also know the caller. Is there any way to determine who called the function?

Answer 1

You can call extended stored procedures from a function.

Some examples are:

• xp_cmdshell
• xp_regwrite
• xp_logevent

If you had the correct permissions, theoretically you could call an extended stored procedure from your function and store information like APP_NAME() and ORIGINAL_LOGIN() in a flat file or a registry key.

Another option is to build an extended stored procedure from scratch.

If all this is too much trouble, I'd follow the early recommendation of SQL Profiler or server side tracing.

An example of using an extended stored procedure is below. This uses xp_logevent to log every instance of the function call in the Windows application log.

One caveat of this method is that if the function is applied to a column in a SELECT query, it will be called for every row that is returned. That means there is a possibility you could quickly fill up the log.

Code:

USE [master]
GO

/* A security risk but will get the job done easily */
GRANT EXECUTE ON xp_logevent TO PUBLIC
GO

/* Test database */
USE [Sandbox]
GO

/* Test function which always returns 1 */
CREATE FUNCTION ufx_Function() RETURNS INT
AS 
BEGIN 

  DECLARE 
    @msg VARCHAR(4000),
    @login SYSNAME,
    @app SYSNAME

  /* Gather critical information */    
  SET @login = ORIGINAL_LOGIN()
  SET @app = APP_NAME()
  SET @msg = 'The function ufx_Function was executed by ' 
    + @login + ' using the application ' + @app

  /* Log this event */
  EXEC master.dbo.xp_logevent 60000, @msg, warning

  /* Resume normal function */
  RETURN 1
END
GO

/* Test */
SELECT dbo.ufx_Function()

Answer 2

try this to search the code:

--declare and set a value of @SearchValue to be your function name
SELECT DISTINCT
    s.name+'.'+o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
        INNER JOIN sys.schemas  s ON o.schema_id=s.schema_id
    WHERE m.definition Like '%'+@SearchValue+'%'
    ORDER BY 1

to find the caller at run time, you might try using CONTEXT_INFO

--in the code chain doing the suspected function call:
    DECLARE @CONTEXT_INFO  varbinary(128)
           ,@Info  varchar(128)
    SET @Info='????'
    SET @CONTEXT_INFO =CONVERT(varbinary(128),'InfoForFunction='+ISNULL(@Info,'')+REPLICATE(' ',128))
    SET CONTEXT_INFO @CONTEXT_INFO

    --after the suspected function call

    SET CONTEXT_INFO 0x0  --reset CONTEXT_INFO


--here is the portion to put in the function:
    DECLARE @Info           varchar(128)
           ,@sCONTEXT_INFO  varchar(128)
    SET @sCONTEXT_INFO=CONVERT(varchar(128),CONTEXT_INFO())

    IF LEFT(@sCONTEXT_INFO,15)='InfoForFunction='
    BEGIN
        SET @Info=RIGHT(RTRIM(@sCONTEXT_INFO),LEN(RTRIM(@sCONTEXT_INFO))-15)
    END

    --use the @Info
    SELECT @Info,@sCONTEXT_INFO

if you put different values in @CONTEXT_INFO in various places, you can narrow down who is calling the function, and refine the value until you find it.

Answer 3

Depending on your current security model. We use connection pooling w/ one sql account. Each application has it's own account to connect to the database. If this is the case. You could then do a Sql Profiler session to find the caller of that function. Whichever account is calling the function will directly relate to one application.

This works for us in the way we handle Sql traffic; I hope it does the same for you.