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

understanding side-effecting operators (like "insert") disallowed in user-defined functions, how 1 log (or otherwise track) calls specific user-defined function? i'd capture parameters passed udf.

ideally, log table information (time stamp , parameter values) each call udf inserted. reports , usage metrics derived table.

i can't rewrite udf stored procedure, of same name, without breaking many downstream systems out in wild expect udf , have no control over.

nor willing enable type of command shell features on our server diminish sql server's best-practice security defaults.

i found solution of problem. it’s little bit tricky , looks hack, seems it’s impossible solve in way.

the idea create .net sql function logs data need (file, windows eventlog, db , on), next create sql udf calls .net function , call sql function functions passing parameters needed logged. sql server doesn't check inside .net function , can write there logic need.

the idea of how create .net sql function without security limitations taken post.

so, create .net library project 1 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 pfx certificate (project properties -> signing tab).

next, call query

use [master] create asymmetric key logkey executable file = 'c:\work\consoleapplication1\sqltest\bin\debug\sqltest.dll'  create login loglogin asymmetric key logkey  grant unsafe assembly loglogin  go  use [mydb] create assembly sqltest 'c:\work\consoleapplication1\sqltest\bin\debug\sqltest.dll'  permission_set = unsafe go create function dbo.log( @data nvarchar(200) ) returns int external name sqltest.[sqltest.logevent].log 

here need change path compiled library, mydb - database name. , create dbo.log sql function. next can call need. example testfunction

create function testfunction (     @p1 int ) returns int begin     declare @temp int     select @temp = [dbo].[log] ('fff')     return 1 end 

so, calling select testfunction(1) write 'fff' text c:\log\logudf.txt file.

that’s it. few important notes:

1. sql server should have permissions (login/user) write file c:\log\logudf.txt.
2. you should sql server admin