Solved

drop proc captured

Posted on 2011-03-08
8
332 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
  • 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 39

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 39

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 39

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 39

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
string fuctions 4 25
2016 SQL Licensing 7 40
SQL view 2 26
optimize stored procedure 6 23
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now