Hello

nice trigger but how can i Disable this trigger I can't find it on my server

i found the solution

 drop trigger trigLogon_CheckForSysAdmin ON ALL SERVER

That deletes it. If you want to leave it in place to be re-enabled later, you'll want to use DISABLE TRIGGER:

DISABLE TRIGGER trigLogon_CheckForSysAdmin ON ALL SERVER;

How can I  audit /Monitor  if  some one Change the SQL server system configuration ?

In 2005 and below, you have to pay careful attention to the SQL Server log. In 2008 you can use Policy Management to check and enforce settings. You can also have it just report on change.

I don't want to monitor all the request that come to the sql cluster/server.

I have a sq 2005 cluster and i want to monitor if some one physically login to the server vis management studio or does some major

things like stop or Make change to the configuration in SQL 2005 cluster.

worst case at least audit who actually logged on to the SQL server cluster or sql 2005 server.

thanks

Audi08

  This trigger does not log a sysadmin logon from windows group. It will be nice that anybody fix this detail

Hello! I think that i found the way to log users from windows group with sysadmin privileges.

CREATE TRIGGER [trigLogon_CheckForSysAdmin]
ON ALL SERVER FOR LOGON
AS
BEGIN
    IF EXISTS (
          SELECT sp.principal_id
            FROM sys.server_role_members srm
            JOIN sys.server_principals sp
                 ON srm.member_principal_id = sp.principal_id
                 AND sp.principal_id not in (285) -- akavila
           WHERE role_principal_id = (
          SELECT principal_id
            FROM sys.server_principals
           WHERE NAME = 'sysadmin')
                 AND ORIGINAL_LOGIN() = sp.NAME)
    BEGIN
      INSERT INTO DBAWork.dbo.AuditSysAdminLogin (EventTime, ServerLogin)
      VALUES (GETDATE(), ORIGINAL_LOGIN())
    END

    -- SysAdmin from Windows Group
    DECLARE @user_windows tinyint;
    SELECT @user_windows =
           CASE WHEN nt_domain = '' THEN 0 ELSE 1 END
      FROM sys.dm_exec_sessions
     WHERE session_id = @@SPID

    IF @user_windows = 1
    BEGIN
      DECLARE @privilege_OUT varchar(10),
              @login varchar(100);

      SET @login = ORIGINAL_LOGIN()
      EXEC xp_logininfo
        @acctname = @login,
        @option = 'all',
        @privilege = @privilege_OUT OUTPUT
      IF @privilege_OUT = 'admin'
      BEGIN
        INSERT INTO DBAWork.dbo.AuditSysAdminLogin (EventTime, ServerLogin)
        VALUES (GETDATE(), ORIGINAL_LOGIN())
      END
    END
END;

Hello,

Can we take this another step where we want to track all DDL and DML activities performed by sysadmin? Is this possible?

 Thanks!

[quote user="tdinh"]Can we take this another step where we want to track all DDL and DML activities performed by sysadmin? Is this possible?[/quote]

To a certain extent. You can track all DDL changes. But in this case you'd be building DDL triggers in each database. You can track INSERT, UPDATE, and DELETE operations using standard DML triggers (INSTEAD OF and AFTER triggers). But you won't be able to track SELECTs using triggers. You'll either have to use the Audit object (if you're on SQL Server 2008 Enterprise Edition) or you'll have to rely on traces.

if IS_SRVROLEMEMBER ('sysadmin') = 1

begin

insert ...

end