SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Audit Drop login


Audit Drop login

Author
Message
Zeal-DBA
Zeal-DBA
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 1125
hello,

what are the different ways to audit if some one drops login, and which would be the best.
please reply its urgent
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5202 Visits: 4076
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
Zeal-DBA
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 1125
thanks for reply, how a DDL trigger will work on it can please elaborate?
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5202 Visits: 4076
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
Zeal-DBA
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 1125
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?
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10058 Visits: 6324
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Zeal-DBA
Zeal-DBA
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 1125
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 ....?
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10058 Visits: 6324
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



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Zeal-DBA
Zeal-DBA
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 1125
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?
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10058 Visits: 6324
ON ALL SERVER details that the trigger is a instance wide trigger and not specific to a database.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search