InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 21:28:06 GMT20http://www.sqlservercentral.com/Forums/Topic1411001-359-1.aspxON ALL SERVER details that the trigger is a instance wide trigger and not specific to a database.Thu, 24 Jan 2013 04:18:55 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1411001-359-1.aspxone 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?Thu, 24 Jan 2013 03:50:49 GMTZeal-DBAhttp://www.sqlservercentral.com/Forums/Topic1411001-359-1.aspxYou will need to change the event from DDL_EVENT to the correct event type which is DDL_LOGIN_EVENTS DDL Event Tree - [url]http://msdn.microsoft.com/en-us/library/bb510452%28v=sql.100%29.aspx[/url]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 thatThu, 24 Jan 2013 03:33:36 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1411001-359-1.aspxanthony 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 ....?Thu, 24 Jan 2013 03:27:02 GMTZeal-DBAhttp://www.sqlservercentral.com/Forums/Topic1411001-359-1.aspxBasic 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[code="sql"]CREATE TRIGGER ................. ON ALL SERVERFOR DDL_EVENTSAS 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[/code]Thu, 24 Jan 2013 03:12:18 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1411001-359-1.aspxas 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?Thu, 24 Jan 2013 03:07:51 GMTZeal-DBAhttp://www.sqlservercentral.com/Forums/Topic1411001-359-1.aspx[quote][b]Zeal-DBA (1/24/2013)[/b][hr]thanks for reply, how a DDL trigger will work on it can please elaborate?[/quote]see this [url]http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/[/url]Thu, 24 Jan 2013 02:53:53 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1411001-359-1.aspxthanks for reply, how a DDL trigger will work on it can please elaborate?Thu, 24 Jan 2013 02:43:21 GMTZeal-DBAhttp://www.sqlservercentral.com/Forums/Topic1411001-359-1.aspxDDL trigger , extended event , profiler trace. etc.Thu, 24 Jan 2013 02:36:49 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1411001-359-1.aspxhello,what are the different ways to audit if some one drops login, and which would be the best.please reply its urgentThu, 24 Jan 2013 02:32:35 GMTZeal-DBA