Audit Drop login

  • hello,

    what are the different ways to audit if some one drops login, and which would be the best.

    please reply its urgent

  • DDL trigger , extended event , profiler trace. etc.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • thanks for reply, how a DDL trigger will work on it can please elaborate?

  • Zeal-DBA (1/24/2013)


    thanks for reply, how a DDL trigger will work on it can please elaborate?

    see this http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • as i have seen this link, it is mainly dfor DDL things like create or drop tables on a database, trigger will we created on database level.. but to capture a info regrads DROP or create loginm there must b a server level trigger define ... mi right?

  • Basic server wide trigger for all DDL events, need to ensure that DB mail is enabled, or change the trigger to insert into an audit table somewhere

    CREATE TRIGGER ................. ON ALL SERVER

    FOR DDL_EVENTS

    AS

    BEGIN

    DECLARE @data XML;

    DECLARE @schema SYSNAME;

    DECLARE @object SYSNAME;

    DECLARE @eventType SYSNAME;

    DECLARE @user SYSNAME;

    DECLARE @Mess NVARCHAR(MAX);

    DECLARE @dbname SYSNAME;

    SET @user = SUSER_NAME()

    SET @data = EVENTDATA();

    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME');

    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME');

    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME');

    SET @dbname = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME');

    SET @Mess =

    '<FONT FACE="Arial"> User = ' + '<B>' + UPPER(CONVERT(SYSNAME, @user)) + '</B>' + ' has done the following DDL operation'

    + '

    '

    + 'DDL Operation = ' + '<B>' + @eventType + '</B>'

    + '

    '

    + 'Object = ' + '<B>' + @dbname + '.' + CONVERT(SYSNAME, @schema) + '.' + CONVERT(SYSNAME, @object) + '</B></FONT>';

    SELECT @Mess

    EXECUTE MSDB.DBO.sp_send_dbmail @profile_name = , @recipients = '', @subject = 'Server level change detected', @body = @Mess, @body_format = 'HTML'

    END

  • anthony thanks for reply, can you please tell me what else changes required in existing script if i only want to cature events CREATE, DROP, ALTER LOGIN only..

    and what info will be catured by this, if a login is droped ....?

  • You will need to change the event from DDL_EVENT to the correct event type which is DDL_LOGIN_EVENTS

    DDL Event Tree - http://msdn.microsoft.com/en-us/library/bb510452%28v=sql.100%29.aspx

    You will need to query the eventdata() to get the data which is contained with in which is an XML format, so you need to pull out the information needed from that

  • one more thing anthony this option "ON ALL SERVER" will enable on a specific instance on which we will create this trigger, or something else will do?

  • ON ALL SERVER details that the trigger is a instance wide trigger and not specific to a database.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply