One of the application team came with a requirement that they don't have a control of who is modifying the schema level changes as the development team have full access in development environment. After discussion, we thought of enabling an audit which traps the necessary information in a table. That can be provided as a report to the application team when needed.I just tried this and it worked fine.
Steps followed:
1. Create a DDL trigger on the datbase which needs to track the schema changes.
2. Create a table on the database which is used by DBA for auditing.
3. Insert that values into the audit table.
4. The table will have data viz Event time, event type, object name, object type, login name, command text and Host Name.
5. So whenever there is a schema change all the above information will be captured and inserted into a different DB which is owned by the DBA team.
Setup:
Creation of trigger in the AppDB:
USE [Appdb]
GO
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
ALTER TRIGGER
[trg_DBEvents]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON
DECLARE
@xmlEventData XML
DECLARE
@hostname NVARCHAR(100)
SET
@xmlEventData = eventdata()
SET
@hostname = HOST_NAME()
INSERT INTO AuditDb.dbo.DDL_Event_log([EventTime], [EventType],
[ObjectName], [ObjectType],[LoginName],
[CommandText],[HostIP])
SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
CONVERT(VARCHAR(100), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
CONVERT(VARCHAR(100),
@xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(100), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(100), @xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')),
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')),
@hostname
END
Creation of table in the AuditDB :
USE [AuditDb]
GO
CREATE TABLE
[dbo].[DDL_Event_log](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[EventTime] [varchar](100) NULL,
[EventType] [varchar](100) NULL,
[ObjectName] [varchar](100) NULL,
[ObjectType] [varchar](100) NULL,
[LoginName] [varchar](100) NULL,
[CommandText] [varchar](max) NULL,
[HostIP] [varchar](50) NULL,
CONSTRAINT
[PK_DDL_Event_log] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [Secondary]
) ON
[Secondary]
GO
Check how it works :
I am going to create a new stored procedure in AppDB database logging in as sql user.
USE [Appdb]
GO
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE
[dbo].[new_dummy_proc]
AS
BEGIN
SELECT * from dbo.inventorydetails where inventoryid is not null;
END
When we see the log table created under the AuditDB database the changes has been captured.
Now I will alter the same procedure with a windows user and see the log table.
Security:
When a new user is created, DBA needs to be provide write access to the AuditDB to capture the ddl changes.
Note:Though creating trigger impact performance, it is accepted as the setup is easy and it is implemented on non-production environment.
