Presents your 
SQL SERVER E-NEWSLETTER for March 6, 2003

<------------------------------------------->

LOG USER ACCESS TO SENSITIVE INFORMATION WITH XP_LOGEVENT

The extended system procedure xp_logevent will log a message in SQL
Server's error log that the user can customize. This message will also be
logged in the Windows NT Event Viewer in the application log. However,
xp_logevent will neither update the @@ERROR global variable nor send a 
message back to the client.

You could use this procedure on a table that has sensitive information
when you want to log user access. This could be accomplished via stored
procedures and/or triggers in an auditing procedure. You may also generate
alerts from the information that's logged into the Windows Event Viewer.

The extended system procedure has three arguments:


* ERROR_NUMBER: This can be from 50000 to 1073741823 and is required.

* MESSAGE: This is a string of up to 8,000 characters and is required.

* SEVERITY: This is an optional argument and may be of one of three
strings: INFORMATIONAL, WARNING, or ERROR.


The following is sample code using xp_logevent within a trigger on a table:

USE NORTHWIND
GO
-- creating a table with sensitive informattion
CREATE TABLE PERSON_BANK
(BANK_ID INT NOT NULL,
PERSON_ID INT NOT NULL,
BANK_ACCOUNT NVARCHAR(200) NOT NULL,
CONSTRAINT pk_PERSON_BANK
   PRIMARY KEY NONCLUSTERED (BANK_ID, PERSON_ID))
GO
-- creating a trigger on table to log accesss of sensitive information.
-- ==============================================
-- Create trigger basic template(After triggger)
-- ==============================================
IF EXISTS (SELECT name
      FROM   sysobjects
      WHERE  name = N'tdui_person_bank'
      AND      type = 'TR')
    DROP TRIGGER tdui_person_bank
GO

CREATE TRIGGER tdui_person_bank
ON Northwind.dbo.person_bank
FOR DELETE, INSERT, UPDATE
AS
BEGIN
-- declare and initialize variables
DECLARE @MSG NVARCHAR(1000)
SELECT @MSG = 'SENSITIVE INFORMATION HAS BEEN ACCESSED.  FOLLOWING IS
MORE DATABASE INFORMATION: ' +
SUBSTRING(APP_NAME(),1,45)+ ' | ' +
SUBSTRING(HOST_NAME(),1,45) + ' | ' +
SUBSTRING(SYSTEM_USER,1,45) + ' | ' +
SUBSTRING(CURRENT_USER,1,45) + ' | ' +
SUBSTRING(OBJECT_NAME(@@PROCID),1,45)
-- log access of sensitive information
EXEC MASTER.DBO.xp_logevent 60000, @MSG, ERROR

END
GO
-- insert into table with sensitive informaation
-- error message should be logged in SQL Seerver error log and event
viewer.
INSERT PERSON_BANK VALUES(1,1,'5242421213334')
GO
-- clean up by dropping table
DROP TABLE PERSON_BANK
GO

----------------------------------------