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.

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.

|improve this answer|||||
-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;
/
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.