Solved

drop proc captured

Posted on 2011-03-08
8
351 Views
Last Modified: 2012-05-11
what are the options to capture who dropped an objects (specifically a proc) when?

thanks
0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 9

Assisted Solution

by:rajeevnandanmishra
rajeevnandanmishra earned 67 total points
ID: 35073002
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35073573
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
 
LVL 40

Expert Comment

by:lcohan
ID: 35073924
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Accepted Solution

by:
lcohan earned 433 total points
ID: 35074001
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
 
LVL 40

Expert Comment

by:lcohan
ID: 35074011
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35077296
thank- now, i am getting error on
Invalid object name 'MyAuditDB..dba_ddl_audit'.

do you have a script to create this table?
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 433 total points
ID: 35083387
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35084527
thanks very much.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question