Sql-server – MS SQL FileTable CreatedBy and LastModifiedBy

filetablesql server

When using a SQL FileTable table, I would like to store additional metadata for the file such as CreatedBy and LastModifiedBy. By looking at SQL File Table Schema, this information is not available by default.

I am wondering if it's possible to determine such information by for example adding a trigger to the table and use current_user to determine current user and store it in a separate table?

I exploring the feasibility of SQL FileTable for a project and have never used this tech.

Best Answer

I was able to solve the problem by using instead triggers.

First, a FileTable has a fixed schema therefore new fields are not allowed. I've created a separate table to store authoring information in a 1-to-1 relationship keyed by 'path_locator'.

Using triggers, I was able to detect authoring information and store it.

When copying, deleting and renaming files, the authoring information (created by, modified by) can be detected by using suser_sname(). However when editing the document in Word, Excel, Notepad, there are scenarios where the suser_sname() will return 'dbo' instead of the credentials of windows credentials. I am not sure why this happens but I worked around it by joining to sys.dm_filestream_non_transacted_handles to get username from there. See code snippet below:

CREATE OR ALTER TRIGGER Authoring_Update ON dbo.DBADocuments AFTER UPDATE AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;

    UPDATE dbo.FileMetadataTable SET ModifiedBy = ISNULL(s.login_name, suser_sname()) 
    FROM dbo.FileMetadataTable f 
    JOIN Inserted i ON i.path_locator = f.PathLocator
    LEFT JOIN sys.dm_filestream_non_transacted_handles s ON f.PathLocator = s.item_id
    LEFT JOIN dbo.FileTable ft WITH (NOLOCK) ON s.item_id = ft.path_locator
END
Related Question