چگونه تغییرات Sp (Stored Procedure) ها در دیتابیس های مختلف را مانیتور کنیم ؟
پرسش
درصورت تغییر sp ها توسط کاربران مختلف و با استفاده از هر ابزاری از جمله management studio چگونه می شود کاربر مجری و تاریخ/ساعت تغییر و نسخه sp قبل از تغییرات دسترسی پیدا کرد؟
پاسخ ( 1 )
/* use utility */
CREATE TABLE dbo.ProcedureChanges
(
EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
EventType NVARCHAR(100),
EventDDL NVARCHAR(MAX),
DatabaseName NVARCHAR(255),
SchemaName NVARCHAR(255),
ObjectName NVARCHAR(255),
HostName NVARCHAR(255),
IPAddress VARCHAR(32),
ProgramName NVARCHAR(255),
LoginName NVARCHAR(255)
);
/*—————————————————-*/
/*initial state*/
/*use destination_db*/
INSERT Utility.dbo.ProcedureChanges
(
EventType,
EventDDL,
DatabaseName,
SchemaName,
ObjectName
)
SELECT
N’Initial control’,
OBJECT_DEFINITION([object_id]),
DB_NAME(),
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
FROM
sys.procedures;
/*————————————————-*/
/* use destination_db for monitoring new changes*/
CREATE TRIGGER CaptureStoredProcedureChanges
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);
SELECT @ip = client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
INSERT Utility.dbo.ProcedureChanges
(
EventType,
EventDDL,
SchemaName,
ObjectName,
DatabaseName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘NVARCHAR(100)’),
@EventData.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘NVARCHAR(MAX)’),
@EventData.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘NVARCHAR(255)’),
@EventData.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘NVARCHAR(255)’),
DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();
END
GO
/*GRANT Permissions*/
USE MASTER
GO
GRANT VIEW SERVER STATE TO [Testefandiari]
/* and : */
/* add users to Utility Db and grant insert permission to ProcedureChanges table*/