drop proc captured

what are the options to capture who dropped an objects (specifically a proc) when?

thanks
LVL 6
anushahannaAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
Sorry that was the security Audit on server level - my bad - heres the DB trigger and works fine on SQL 2005 as well - here what is logs:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [My_ddl_events]
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
if @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')  
            NOT      IN (
                              'ALTER_AUTHORIZATION_DATABASE'
                              ,'ALTER_USER'
                              ,'CREATE_ROLE'
                              ,'CREATE_SCHEMA'
                              ,'CREATE_USER'
                              ,'DENY_DATABASE'
                              ,'DROP_ROLE'
                              ,'DROP_SCHEMA'
                              ,'DROP_USER'
                              ,'GRANT_DATABASE'
                              ,'REVOKE_DATABASE'
                              ,'UPDATE_STATISTICS'
                        )
      and (@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') + ':' + @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)')) <> 'NT AUTHORITY\SYSTEM:dbo'
INSERT MyAuditDB..dba_ddl_audit (posted_on, db_user, event_logged, transact_sql,DatabaseName,SchemaName,ObjectName,ObjectType)
   VALUES       (--GETDATE(),
                  @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
                  @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') + ':' + @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)') ,
                  @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
                  'database - ' + @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)') + ': ' + @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
                  @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)'),
                  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(50)') );


GO
0
 
rajeevnandanmishraConnect With a Mentor Commented:
Hi,

Normally the "drop proc" statement can be issued only by dbo.
If you have SQL Server 2K8, then you can use DDL triggers.
Otherwise, you can use tools like "ApexSQL Audit" that can provide you these informations.

0
 
anushahannaAuthor Commented:
could you tell me what is wrong with
it does not like "DROP_PROC"
CREATE TRIGGER [db_LOG]
ON DATABASE
FOR DROP_PROC
AS
      SET NOCOUNT ON
      DECLARE @xEvent XML
      SET @xEvent = eventdata() 
      INSERT INTO dbLOG VALUES(
            REPLACE(CONVERT(VARCHAR(50), @xEvent.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
            CONVERT(VARCHAR(25), @xEvent.query('data(/EVENT_INSTANCE/ServerName)')),
            CONVERT(VARCHAR(15), @xEvent.query('data(/EVENT_INSTANCE/UserName)')),
            CONVERT(VARCHAR(MAX), @xEvent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
            )

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
lcohanDatabase AnalystCommented:
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_INSTANCE/LoginName)[1]', 'nvarchar(100)') + ':' + @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)')) <> 'NT AUTHORITY\SYSTEM:dbo'
      INSERT MyAuditDB..dba_security_audit (posted_on, db_user, event_logged, transact_sql,DatabaseName,SchemaName,ObjectName,ObjectType)
      VALUES       (--GETDATE(),
                  @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
                  @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') + ':' + @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)') ,
                  @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
                  'database - ' + @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)') + ': ' + @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
                  @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)'),
                  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(50)') );

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [OneShopSVR_DDL_LOGIN_EVENTS] ON ALL SERVER
GO


0
 
lcohanDatabase AnalystCommented:
event_logged
ALTER_AUTHORIZATION_DATABASE
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
0
 
anushahannaAuthor Commented:
thank- now, i am getting error on
Invalid object name 'MyAuditDB..dba_ddl_audit'.

do you have a script to create this table?
0
 
lcohanConnect With a Mentor Database AnalystCommented:
sure - here it is and replace MyAuditDB with one of your own DB's:

USE [MyAuditDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[dba_ddl_audit](
      [posted_on] [datetime] NULL,
      [db_user] [nvarchar](500) NULL,
      [event_logged] [nvarchar](500) NULL,
      [transact_sql] [nvarchar](4000) NULL,
      [id] [int] IDENTITY(1,1) NOT NULL,
      [DatabaseName] [varchar](100) NULL,
      [SchemaName] [varchar](100) NULL,
      [ObjectName] [varchar](255) NULL,
      [ObjectType] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
      [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

0
 
anushahannaAuthor Commented:
thanks very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.