Audit Drop login

Question

Post reply

Audit Drop login

Zeal-DBA

SSCarpal Tunnel

Points: 4332
More actions
January 24, 2013 at 2:32 am

#394012

hello,
what are the different ways to audit if some one drops login, and which would be the best.
please reply its urgent

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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;-)

Zeal-DBA SSCarpal Tunnel Points: 4332 More actions · Answer 2

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

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;-)

Zeal-DBA SSCarpal Tunnel Points: 4332 More actions · Answer 4

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?

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

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 =

' User = ' + '' + UPPER(CONVERT(SYSNAME, @user)) + '' + ' has done the following DDL operation'

+ '

'

+ 'DDL Operation = ' + '' + @eventType + ''

+ '

'

+ 'Object = ' + '' + @dbname + '.' + CONVERT(SYSNAME, @schema) + '.' + CONVERT(SYSNAME, @object) + '';

SELECT @Mess

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

END

Zeal-DBA SSCarpal Tunnel Points: 4332 More actions · Answer 6

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 ....?

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

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

Zeal-DBA SSCarpal Tunnel Points: 4332 More actions · Answer 8

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?

Ant-Green SSC Guru Points: 113507 More actions · Answer 9

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