5

I need a table to put login and logout information for client not sysadmin,then i can use date to statistic user login/off ration...

Can anyone offer me a simple method to create login and logout trigger for audit specific client not the sysadmin,

Thanks in advance.

user28748
  • 51
  • 1
  • 1
  • 2
  • See if this answer helps : (http://dba.stackexchange.com/a/54897/8783). The idea is same, just differ in the logic – Kin Shah Dec 26 '13 at 18:54

2 Answers2

5

You can add a simple IF statement to your LOGON trigger, as such:

USE test;
GO
CREATE TABLE LogonAudit
(
    AuditID INT NOT NULL CONSTRAINT PK_LogonAudit_AuditID 
                PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , UserName NVARCHAR(255)
    , LogonDate DATETIME
    , spid INT NOT NULL
);
GO
GRANT INSERT ON Test.dbo.LogonAudit TO public;
GO
CREATE TRIGGER MyLogonTrigger ON ALL SERVER FOR LOGON
AS 
BEGIN
    IF SUSER_SNAME() <> 'sa'
    INSERT INTO Test.dbo.LogonAudit (UserName, LogonDate, spid) 
            VALUES (SUSER_SNAME(), GETDATE(), @@SPID);
END;
GO
ENABLE TRIGGER MyLogonTrigger ON ALL SERVER;

This will only audit logons for users not named 'sa' by virtue of the line IF SUSER_SNAME() <> 'sa'

Alternatively, if you want to ONLY audit the login of a specific user, you could write the trigger like:

CREATE TRIGGER MyLogonTrigger ON ALL SERVER FOR LOGON
AS 
BEGIN
    IF SUSER_SNAME() = 'SomeUser'
    INSERT INTO Test.dbo.LogonAudit (UserName, LogonDate, spid) 
            VALUES (SUSER_SNAME(), GETDATE(), @@SPID);
END;

This will only record when SomeUser logs into the server.

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
-2
create table
stats$user_log
(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8)
)
;
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;
/
Tom V
  • 15,670
  • 7
  • 63
  • 86
Jaimit
  • 1