Re: Logon triggers

Hugo
See if this gives you a start


create trigger AuditLogin_Demo
on all server
with execute as self
for logon
as begin

IF IS_SRVROLEMEMBER ('sysadmin') = 1

insert into Yourtable (....) values (ORIGINAL_LOGIN(),APP_NAME ( ) )
end
go


EXECUTE AS LOGIN ='loginname'

GO

IF IS_SRVROLEMEMBER ('sysadmin') = 1

PRINT 'YES'

ELSE

PRINT 'NO'

GO

REVERT



"Hugo" <Hugo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:808E011D-0218-4C45-B34E-41176B4BFA0E@xxxxxxxxxxxxxxxx
Hello,

I am searching for the best way to log all logins using sysadmin users. I
want to record the time, username, hostname and application-name. There
are
to many users with sysadmin rights and I need some for info from the
system
before changing that. I am running SQL 2005 SP2.

I am looking at logon triggers, but I understand that if the trigger fails
(like when my audit table is unavailable) no one (or at least no
sysadmins)
will have access to the server. Is this correct? How to resolve?

Thank you very much,

Hugo



.

Relevant Pages