creating logon & logoff triggers in MSSQL

Question

creating logon & logoff triggers in MSSQL

Glen_A

SSC Eights!

Points: 988
More actions
June 3, 2013 at 8:02 am

#294667

We have several database that we track user logons for, until now all these databases resided in Oracle. For Oracle I created a sys trigger than logs os user, database user, program used, etc., as well as logon time & logoff time that writes to a table I called "audlog" then I query the table every month
This is what I created in Oracle:
CREATE TABLE audlog.session_audit
(user_name VARCHAR2(30),
log_date DATE,
session_id VARCHAR2(30),
ip_addr VARCHAR2(30),
hostname VARCHAR2(30),
auth_type VARCHAR2(30),
os_user VARCHAR2(30),
event VARCHAR2(8),
program VARCHAR2(30))
tablespace users;
Create Or Replace Trigger Trg_Logon_Info
After Logon On Database
Declare
Program Varchar2(30);
Begin
Select program into program
From v$session
Where sid=(Select Max(Sid) From V_$Mystat);
Insert Into Audlog.Session_Audit
Values
( user
, sysdate
, Sys_Context ('USERENV', 'SESSIONID')
, Sys_Context ('USERENV', 'IP_ADDRESS')
, Sys_Context ('USERENV', 'HOST')
, Sys_Context ('USERENV', 'AUTHENTICATION_TYPE')
, Sys_Context ('USERENV', 'OS_USER')
, 'LOG ON' -- Event
, Program
);
End;
/
Create Or Replace Trigger Trg_Logoff_Info
before Logoff On Database
Declare
Program Varchar2(30);
Begin
Select Program Into Program
From V$Session
Where Sid=(Select Max(Sid) From V_$Mystat);
Insert Into Audlog.Session_Audit
Values
( user
, sysdate
, Sys_Context ('USERENV', 'SESSIONID')
, Sys_Context ('USERENV', 'IP_ADDRESS')
, Sys_Context ('USERENV', 'HOST')
, Sys_Context ('USERENV', 'AUTHENTICATION_TYPE')
, Sys_Context ('USERENV', 'OS_USER')
, 'LOG OFF' -- Event
, Program
);
End;
I would like to create something in MSSQL that basically shows the same information, so I can keep the reports consistent looking.
Can I duplicate this trigger in MSSQL?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There are login triggers in SQL Server. Have a look in Books Online for the details and examples, they're a form of DDL trigger. There are no logoff triggers though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

Also Logon Triggers are per server, and not at the database level, so i can login, and then access multiple databases with multiple commands, and a Logon trigger would not capture anything done within the databases...just at the moment of the initial connection.

depending on what you want to audit in a database, you might want to look at a trace or extended event to capture accessing of objects within a database;

Depending on the version you are running, SQL also provides a lot of auditing tools like SQL Auditing, C2 Level auditing, Change Tracking and Change Data Capture, as well as building your own via triggers, traces and extended events.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Glen_A SSC Eights! Points: 988 More actions · Answer 3

thanks for the replies - finally getting back to this task.

Basically what I want to do is find out who connected to a database, what date & time they connected, what machine they connected from and which program they used to connect with (SSMS, Excel, custom app etc,) At this point I don't need to know what they did while they were connected to the database.

I can live without the logoff trigger if I have too.

Been reading a few articles on this, but have not found one yet that pertains to what I am looking for. It seems that a logon or connection trigger to one certain database is not very common, or perhaps not even possible?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

The main issue is that a login occurs to the instance. There isn't an access point I'm aware of that traps your access to a database. You might connect directly to one after login, you might issues a USE, or do a cross database query. AFAIK, you can't really capture that someone logged into a database since they don't log into a database.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Just to drive the point home, consider what would need to be tracked at the database level if a query were issued that joined tables from multiple databases.

Trace can track log off events if you really need it.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Glen_A SSC Eights! Points: 988 More actions · Answer 6

Thanks - well I found an example of a script here[/url], and with a little editing to suit my needs this is what I have come up with so far: /* Create Audit Database */CREATE DATABASE AuditDbGOUSE AuditDbGO/* Create Audit Table */CREATE TABLE [dbo].[ServerLogonHistory]( [SystemUser] [varchar](512) NULL, [DBUser] [varchar](512) NULL, [APP_NAME] [varchar](512) NULL, [SPID] [int] NULL, [LogonTime] [datetime] NULL, [HOST_NAME] [varchar](512) NULL) ON [PRIMARY]GO/* Create Logon Trigger */CREATE TRIGGER [Tr_ServerLogon]ON ALL SERVER FOR LOGONASBEGINif( (ORIGINAL_LOGIN() <> 'sa')and APP_NAME() not like 'SQLAgent%')INSERT INTO AuditDb.dbo.ServerLogonHistorySELECT SYSTEM_USER,USER,APP_NAME(),@@SPID,GETDATE(),HOST_NAME()ENDGOSeems to work ok, filtered out any sa or sqlagent connections but I am getting multiple logon records for every session I open:select * from ServerLogonHistory;SystemUser DBUser APP_NAME SPID LogonTime HOST_NAMEaccount\user dbo Microsoft SQL Server Management Studio - Query 52 2013-06-10 15:36:38.160 WORKSTATIONaccount\user dbo Microsoft SQL Server Management Studio - Transact-SQL IntelliSense 59 2013-06-10 15:36:39.543 WORKSTATIONaccount\user dbo Microsoft SQL Server Management Studio - Transact-SQL IntelliSense 59 2013-06-10 15:36:39.553 WORKSTATIONaccount\user dbo Microsoft SQL Server Management Studio - Query 59 2013-06-10 16:20:06.613 WORKSTATIONaccount\user dbo Microsoft SQL Server Management Studio - Transact-SQL IntelliSense 60 2013-06-10 16:20:08.010 WORKSTATIONaccount\user dbo Microsoft SQL Server Management Studio - Transact-SQL IntelliSense 60 2013-06-10 16:20:08.020 WORKSTATIONIt seems like the intellisense service is also creating a connection(s) for every session. Not sure what this service does and if I can disable it safely. More reading yet I guess 🙂