SQL calling function lookup

Question

There is an SQL function that I would like to remove from the SQL Server 2005 database, but first I would like to make sure that no one calls it. I used the View Dependencies feature to remove any link to it from the database. However, it can be web applications or SSIS packages.

My idea was for the function to insert an entry into the audit table each time it was called. However, this will be of limited value if I also do not know the caller. Is there any way to determine who called the function?

Answer 1

You can call extended stored procedures from a function .

Here are some examples:

• 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 such as APP_NAME () and ORIGINAL_LOGIN () in a simple file or in a registry key.

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

If all this has too many problems, I would follow an early recommendation of SQL Profiler or server side tracing .

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

One caveat to this method is that if a function is applied to a column in a SELECT query, it will be called for each row returned. This means that it is possible to quickly fill out a journal.

The 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 searching for 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 runtime you can 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 different places, you can narrow down who calls the function and refine the value until you find it.

Answer 3

Depending on the current security model. We use pooling with a single sql account. Each application has its own account to connect to the database. If it is true. You can then run an Sql Profiler session to find the caller of this feature. No matter which account calls the function, it will be directly linked to one application.

This works for us in the way we handle Sql traffic; Hope he does the same for you.

Answer 4

You can try using APP_NAME () and USER_NAME (). This will not give you specifics (for example, the name of the SSIS package), but it may help.

Answer 5

Another less elegant way is grep -R [functionname] * via the source code. This may or may not be workable depending on the amount of code.

This has the advantage of working, even if this part is only rarely used, which will be a big problem with your idea of ​​an audit table.

Answer 6

This will help you find what it is called anywhere in your database.

 select object_name(id) from sys.syscomments where text like '%**<FunctionName>**%' 

Answer 7

You can run the trace in the profiler to find out if this function is called within a week (or something that you think is a safe window).

I think that you can also use OPENROWSET to call the SP, which is registered in the table if you include special queries.