anushahanna
asked on
drop proc captured
what are the options to capture who dropped an objects (specifically a proc) when?
thanks
thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here's my version and please note it relies and populates a table in a MyAuditDB database:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [My_DDL_LOGIN_EVENTS]
ON All Server
FOR
DDL_LOGIN_EVENTS
AS
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
if (@data.value('(/EVENT_INST ANCE/Login Name)[1]', 'nvarchar(100)') + ':' + @data.value('(/EVENT_INSTA NCE/UserNa me)[1]', 'nvarchar(100)')) <> 'NT AUTHORITY\SYSTEM:dbo'
INSERT MyAuditDB..dba_security_au dit (posted_on, db_user, event_logged, transact_sql,DatabaseName, SchemaName ,ObjectNam e,ObjectTy pe)
VALUES (--GETDATE(),
@data.value('(/EVENT_INSTA NCE/PostTi me)[1]', 'datetime'),
@data.value('(/EVENT_INSTA NCE/LoginN ame)[1]', 'nvarchar(100)') + ':' + @data.value('(/EVENT_INSTA NCE/UserNa me)[1]', 'nvarchar(100)') ,
@data.value('(/EVENT_INSTA NCE/EventT ype)[1]', 'nvarchar(100)'),
'database - ' + @data.value('(/EVENT_INSTA NCE/Databa seName)[1] ', 'nvarchar(100)') + ': ' + @data.value('(/EVENT_INSTA NCE/TSQLCo mmand)[1]' , 'nvarchar(2000)'),
@data.value('(/EVENT_INSTA NCE/Databa seName)[1] ', 'nvarchar(100)'),
@data.value('(/EVENT_INSTA NCE/Schema Name)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTA NCE/Object Name)[1]', 'nvarchar(255)'),
@data.value('(/EVENT_INSTA NCE/Object Type)[1]', 'nvarchar(50)') );
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [OneShopSVR_DDL_LOGIN_EVEN TS] ON ALL SERVER
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [My_DDL_LOGIN_EVENTS]
ON All Server
FOR
DDL_LOGIN_EVENTS
AS
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
if (@data.value('(/EVENT_INST
INSERT MyAuditDB..dba_security_au
VALUES (--GETDATE(),
@data.value('(/EVENT_INSTA
@data.value('(/EVENT_INSTA
@data.value('(/EVENT_INSTA
'database - ' + @data.value('(/EVENT_INSTA
@data.value('(/EVENT_INSTA
@data.value('(/EVENT_INSTA
@data.value('(/EVENT_INSTA
@data.value('(/EVENT_INSTA
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [OneShopSVR_DDL_LOGIN_EVEN
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
event_logged
ALTER_AUTHORIZATION_DATABA SE
ALTER_FUNCTION
ALTER_INDEX
ALTER_PROCEDURE
ALTER_SCHEMA
ALTER_TABLE
ALTER_TRIGGER
ALTER_USER
ALTER_VIEW
CREATE_FUNCTION
CREATE_INDEX
CREATE_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME
CREATE_PROCEDURE
CREATE_ROLE
CREATE_SCHEMA
CREATE_STATISTICS
CREATE_TABLE
CREATE_TRIGGER
CREATE_TYPE
CREATE_USER
CREATE_VIEW
DENY_DATABASE
DROP_FUNCTION
DROP_INDEX
DROP_PARTITION_FUNCTION
DROP_PROCEDURE
DROP_ROLE
DROP_SCHEMA
DROP_STATISTICS
DROP_TABLE
DROP_TRIGGER
DROP_USER
DROP_VIEW
GRANT_DATABASE
REVOKE_DATABASE
UPDATE_STATISTICS
ALTER_AUTHORIZATION_DATABA
ALTER_FUNCTION
ALTER_INDEX
ALTER_PROCEDURE
ALTER_SCHEMA
ALTER_TABLE
ALTER_TRIGGER
ALTER_USER
ALTER_VIEW
CREATE_FUNCTION
CREATE_INDEX
CREATE_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME
CREATE_PROCEDURE
CREATE_ROLE
CREATE_SCHEMA
CREATE_STATISTICS
CREATE_TABLE
CREATE_TRIGGER
CREATE_TYPE
CREATE_USER
CREATE_VIEW
DENY_DATABASE
DROP_FUNCTION
DROP_INDEX
DROP_PARTITION_FUNCTION
DROP_PROCEDURE
DROP_ROLE
DROP_SCHEMA
DROP_STATISTICS
DROP_TABLE
DROP_TRIGGER
DROP_USER
DROP_VIEW
GRANT_DATABASE
REVOKE_DATABASE
UPDATE_STATISTICS
ASKER
thank- now, i am getting error on
Invalid object name 'MyAuditDB..dba_ddl_audit' .
do you have a script to create this table?
Invalid object name 'MyAuditDB..dba_ddl_audit'
do you have a script to create this table?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks very much.
ASKER
it does not like "DROP_PROC"
Open in new window