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