SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Auditing users who accessed database question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Glen_S
Starting Member

Canada
4 Posts

Posted - 05/28/2013 :  19:01:23  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 05/29/2013 :  01:11:34  Show Profile  Reply with Quote
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

Canada
4 Posts

Posted - 05/29/2013 :  09:44:30  Show Profile  Reply with Quote
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?

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;

Edited by - Glen_S on 05/29/2013 09:47:18
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000