Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Audit Drop login Expand / Collapse
Author
Message
Posted Thursday, January 24, 2013 2:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:52 PM
Points: 208, Visits: 1,020
hello,

what are the different ways to audit if some one drops login, and which would be the best.
please reply its urgent
Post #1411001
Posted Thursday, January 24, 2013 2:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1411004
Posted Thursday, January 24, 2013 2:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:52 PM
Points: 208, Visits: 1,020
thanks for reply, how a DDL trigger will work on it can please elaborate?
Post #1411010
Posted Thursday, January 24, 2013 2:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1411014
Posted Thursday, January 24, 2013 3:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:52 PM
Points: 208, Visits: 1,020
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?
Post #1411018
Posted Thursday, January 24, 2013 3:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,131, Visits: 4,918
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
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

Post #1411019
Posted Thursday, January 24, 2013 3:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:52 PM
Points: 208, Visits: 1,020
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 ....?
Post #1411024
Posted Thursday, January 24, 2013 3:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,131, Visits: 4,918
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
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

Post #1411028
Posted Thursday, January 24, 2013 3:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:52 PM
Points: 208, Visits: 1,020
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?
Post #1411034
Posted Thursday, January 24, 2013 4:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,131, Visits: 4,918
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
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

Post #1411050
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse