Track or log calls to a user-defined function in SQL Server

Question

Understanding that side-effecting operators (like "insert") are disallowed in user-defined functions, how does one log (or otherwise track) calls to a specific user-defined function? I'd also like to capture the parameters passed into the UDF.

Ideally, the log would be a table into which information (time stamp and parameter values) about each call to the UDF is inserted. Reports and usage metrics could then be derived from that table.

I can't rewrite the UDF as a stored procedure, even of the same name, without breaking many downstream systems that are out in the wild that expect a UDF and that I have no control over.

Nor am I willing to enable any type of command shell features on our server that will diminish SQL Server's best-practice security defaults.

Answer 1

I found solution of your problem. It’s a little bit tricky and looks like a hack, but it seems it’s impossible to solve in another way.

The idea is to create a .NET SQL function which logs data where you need (file, Windows EventLog, db and so on), next create SQL UDF which calls this .NET function and finally call this SQL function from your functions passing all parameters needed to be logged. SQL Server doesn't check what is inside .net function and you can write there all logic you need.

The idea of how to create a .net SQL function without any security limitations is taken from this post.

So, create a .net library project with this one file

using System;

namespace SqlTest
{
 public class LogEvent
 {
    [Microsoft.SqlServer.Server.SqlFunction]
    public static int Log(string data)
    {
        System.IO.File.AppendAllText(@"C:\Log\LogUDF.txt", data);
        return 0;
    }
 }
}

Sign it with some pfx certificate (project properties -> signing tab).

Next, call this query

USE [master]
CREATE ASYMMETRIC KEY LogKey FROM EXECUTABLE FILE =
'C:\Work\ConsoleApplication1\SqlTest\bin\Debug\SqlTest.dll'

CREATE LOGIN LogLogin FROM ASYMMETRIC KEY LogKey

GRANT UNSAFE ASSEMBLY TO LogLogin 
GO

USE [MyDB]
CREATE ASSEMBLY SqlTest FROM
'C:\Work\ConsoleApplication1\SqlTest\bin\Debug\SqlTest.dll' 
WITH PERMISSION_SET = unsafe
GO
CREATE FUNCTION dbo.Log( @data as nvarchar(200) )
RETURNS int
AS EXTERNAL NAME SqlTest.[SqlTest.LogEvent].Log

Here you need to change path to your compiled library, MyDB - your database name. And you will create dbo.Log SQL function. Next you can call it where you need. For example like from this TestFunction

CREATE FUNCTION TestFunction
(
    @p1 int
)
RETURNS int
AS
BEGIN
    DECLARE @temp int
    SELECT @temp = [dbo].[Log] ('fff')
    RETURN 1
END

So, calling SELECT TestFunction(1) will write 'fff' text to C:\Log\LogUDF.txt file.

That’s it. A few important notes:

1. SQL server should have permissions (login/user) to write into file C:\Log\LogUDF.txt.
2. You should be SQL server admin

Answer 2

You can try the following:

1) Use SQL Profiler to check caught data for each of your different scenarios

Check SP:StmtCompleted to ensure that you catch the statements that execute within the stored procedure or used defined functions. Also make sure you include all required columns (TextData, LoginName, ApplicationName etc.). TextData is essential for this solution.

2) Check each scenario to see what you receive in the profiler. E.g.:

-- a mock function that is similar to what I have understood your function does
alter FUNCTION dbo.GetLoginResult(@username VARCHAR(64))
    RETURNS INT
AS
BEGIN
    DECLARE @l INT = LEN(@username)

    IF (@l < 10)
        RETURN 0

    RETURN 1

    -- DECLARE @Result INT
    -- SELECT @Result = DATEPART(s, GETDATE()) % 3
    -- RETURN @Result
END
go

select dbo.GetLoginResult('SomeGuy')  --> `IF (@l < 10)` and `RETURN 0`
GO

select dbo.GetLoginResult('Some girl with a long name')  --> `IF (@l < 10)` and `RETURN 1`
GO

So, if you can adapt your function to be written in a such a way that a specific instruction is executed when a particular output is about to be returned, you can recognize what is the result of the function based on the profiled information (as input and output values do not seem to be caught in the profiler)

3) Server-side tracing

As already suggested, SQL Profiler puts significant overhead, so you should use server-side tracing. Luckly, you can export just created profiling information as indicated here:

i) SQL Profiler -> File -> Export -> Script Trace Definition -> For SQL Server ..

ii) Replace the path in the generated script and run it -> remember generated id (it is trace id)

iii) you can open the file in the profiler and export its data to a table, after stopping the trace (it is locked by sqlserver process).