Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Auditing users who accessed database question

Author  Topic 

Glen_S
Starting Member

4 Posts

Posted - 2013-05-28 : 19:01:23
We have several database that we track user logins for, until now all these databases resided in Oracle. For Oracle I created a sys trigger than logs os user, database user, program used, login time, logout time that writes to a table I called "audlog" then I query the table every month

What's the easiest way to do this in MSSQL so I can get a similar looking report for the bean counters?


thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 01:11:34
Sounds like LOGON trigger is what you're after

http://msdn.microsoft.com/en-us/library/bb326598.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Glen_S
Starting Member

4 Posts

Posted - 2013-05-29 : 09:44:30
thanks - yes that something like what I need. If possible I want it to mimic the oracle triggers I have as close as possible so reports look the same. Here is what I did in Oracle - (I'm not that familiar with MSSQL and it's inner workings yet)

apologies for code window not showing - can't seem to get the
 tags working?

[code]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;
Go to Top of Page
   

- Advertisement -