In today’s digital world, security is of utmost importance. Many applications require logging changes made to the database in order to track who did what. SQL Server provides a powerful feature called triggers to accomplish this goal. Triggers can be developed for UPDATE, DELETE, and INSERT events, and each time a record is changed, the result of the SUSER_SNAME() function can be stored in a field.

However, there is a challenge when it comes to using a single account to access the database. Many service providers and companies restrict the usage of SQL Server logins for security reasons. So, how can we log changes and stay within the limitations of a single account?

The answer lies in the SET CONTEXT_INFO command in SQL Server 2000. This command allows us to associate up to 128 bytes of binary information with the current session or connection. Each connection can have its own unique value set, making it easy to track changes and retrieve the login name.

Let’s take a look at an example of how to implement this system:

CREATE TABLE tblUsers
(
    ID int IDENTITY(1,1),
    Login varchar(50) NOT NULL,
    FirstName varchar(50) NULL,
    LastName varchar(50) NULL
)

INSERT INTO tblUsers (Login, FirstName, LastName)
VALUES ('jsmith', 'John', 'Smith'),
       ('mdavis', 'Michael', 'Davis'),
       ('rjohns', 'Richard', 'Johns')

CREATE PROCEDURE uspSetContextLogin
    @Login varchar(50) = ''
AS
BEGIN
    DECLARE @ContextInfo varbinary(128)
    
    IF @Login <> ''
    BEGIN
        SELECT @ContextInfo = CONVERT(varbinary(128), ID) 
        FROM tblUsers 
        WHERE Login = @Login
        
        IF @ContextInfo IS NULL 
            SET @ContextInfo = 0
        
        SET CONTEXT_INFO @ContextInfo
        
        SELECT @ContextInfo AS ContextInfo, CONVERT(int, @ContextInfo) AS INTContextInfo
    END
    ELSE
        SELECT ''
END

CREATE FUNCTION dbo.fnGetContextLogin()
RETURNS varchar(50)
AS
BEGIN
    DECLARE @Login varchar(50)
    
    SELECT @Login = Login 
    FROM tblUsers 
    WHERE CONVERT(varbinary(128), ID) = (SELECT context_info FROM master..sysprocesses(NOLOCK) WHERE spid = @@SPID)
    
    IF @Login IS NULL 
        SET @Login = SUSER_SNAME()
    
    RETURN @Login
END

CREATE TABLE tblProducts
(
    ID int IDENTITY,
    Name varchar(50),
    Quantity int,
    Price money,
    CreatedBy varchar(50) DEFAULT dbo.fnGetContextLogin(),
    ChangedBy varchar(50) DEFAULT dbo.fnGetContextLogin(),
    Created datetime DEFAULT GETDATE(),
    Changed datetime DEFAULT GETDATE()
)

CREATE TRIGGER UTrig_tblProducts ON tblProducts
FOR UPDATE
AS
BEGIN
    UPDATE tblProducts
    SET ChangedBy = dbo.fnGetContextLogin(),
        Changed = GETDATE()
    FROM inserted i
    WHERE i.ID = tblProducts.ID
END

With this system in place, we can now track changes in the tblProducts table using only a single SQL account to connect to the database. Each time a connection is established, the context should be set to the current login name using the uspSetContextLogin stored procedure. This allows us to retrieve the current login name using the fnGetContextLogin function.

For example, let’s say John Smith logs in to the application with his login name ‘jsmith’. After establishing the connection, we can set the context using the uspSetContextLogin stored procedure:

EXEC uspSetContextLogin 'jsmith'

Now, whenever a new record is created in the tblProducts table, the CreatedBy and ChangedBy fields will be automatically set to the current login name. Similarly, when an update is made, the ChangedBy field will be updated accordingly.

By utilizing the SET CONTEXT_INFO command, we have overcome the limitations of a single account and can effectively track changes in the database. It is important to note that setting the context should only be done for UPDATE and INSERT operations, and not for SELECTs, to minimize any impact on system performance.

In conclusion, the SET CONTEXT_INFO command in SQL Server provides a solution for implementing a single account security audit trail system. By setting the context for each session, we can track changes and retrieve the login name, even when restricted to using a single account.

Thank you for reading!