Auditing users who accessed database question

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

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources