My issue: executing sp_refreshsqlmodule
will update the sys.objects.modify_date
value, but does not fire a DDL event that can be used by a DDL trigger. So if using a DDL trigger to show object change history, I cannot reconcile the reason that a procedure, function, etc. would have a recent modify_date
when no ALTER
statement has been run against the object. This can cause some headaches during audit time.
My question: Is there any way to capture sp_refreshsqlmodule
event, so that I can have an automated way to reconcile the modify_date
for database objects?
I was able to find this connect item for SQL Server 2008 https://connect.microsoft.com/SQLServer/feedback/details/340441/sp-autostats-and-other-system-procedures-does-not-fire-ddl-triggers , which is marked as closed with the comment:
we dont think it is critical to fix this for SQL Server 2008. We will definitely consider it for the release after that
I am able to replicate the issue in SQL Server 2014 as well.
SQL script to replicate the issue:
IF OBJECT_ID('dbo.test_DDL_log ') IS NOT NULL
DROP TABLE dbo.test_DDL_log;
GO
CREATE TABLE dbo.test_DDL_log (id int not null identity primary key, DDL_EventData xml, dateCreated datetime, contextInfo varchar(128));
go
CREATE TRIGGER [TEST_ddlDatabaseTriggerLog]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
--Log all DDL operations on this database to an audit table.
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.test_DDL_log(DDL_EventData, dateCreated, contextInfo)
VALUES (
EVENTDATA()
, GETDATE()
, REPLACE(CAST(CONTEXT_INFO() AS VARCHAR(128)), CHAR(0), '')
);
END
GO
--Context info used in DDL trigger to tie to build and/or Support Ticket # to this change
DECLARE @c varbinary(128);
SET @c=cast('Ticket 123 v1.2.345' as varbinary(128));
SET CONTEXT_INFO @c;
GO
--Repro Example taken from: https://msdn.microsoft.com/en-us/library/bb326754.aspx
-- Create an alias type.
IF EXISTS (SELECT 'TEST_mytype' FROM sys.types WHERE name = 'TEST_mytype')
DROP TYPE TEST_mytype;
GO
CREATE TYPE TEST_mytype FROM nvarchar(5);
GO
IF OBJECT_ID ('dbo.TEST_to_upper', 'FN') IS NOT NULL
DROP FUNCTION dbo.TEST_to_upper;
GO
CREATE FUNCTION dbo.TEST_to_upper (@a TEST_mytype)
RETURNS TEST_mytype
WITH ENCRYPTION
AS
BEGIN
RETURN upper(@a)
END;
GO
-- Increase the length of the alias type.
EXEC sp_rename 'TEST_mytype', 'TEST_myoldtype', 'userdatatype';
GO
CREATE TYPE TEST_mytype FROM nvarchar(10);
GO
---- The function parameter still uses the old type.
-- and would Fail here because of truncation:
--SELECT dbo.TEST_to_upper('abcdefgh');
GO
select modify_date_BEFORE = o.modify_date
from sys.objects o
where o.name = 'TEST_to_upper'
;
go
WAITFOR DELAY '00:00:05'; --allow some time to elapse so that modify_date change is more noticable
GO
-- Refresh the function to bind to the renamed type.
EXEC sys.sp_refreshsqlmodule 'dbo.TEST_to_upper';
go
select modify_date_AFTER = o.modify_date
from sys.objects o
where o.name = 'TEST_to_upper'
;
go
--only 4 events show here, NOT included the call to sp_refreshsqlmodule which updated the modify_date
SELECT * from dbo.test_DDL_log;
go
--CLEANUP
IF OBJECT_ID ('dbo.TEST_to_upper', 'FN') IS NOT NULL
DROP FUNCTION dbo.TEST_to_upper;
GO
IF EXISTS (SELECT 'TEST_myoldtype' FROM sys.types WHERE name = 'TEST_myoldtype')
DROP TYPE TEST_myoldtype;
GO
IF EXISTS (SELECT 'TEST_mytype' FROM sys.types WHERE name = 'TEST_mytype')
DROP TYPE TEST_mytype;
GO
if exists(select 1 from sys.triggers t where t.name = 'TEST_ddlDatabaseTriggerLog')
DROP TRIGGER [TEST_ddlDatabaseTriggerLog] ON DATABASE
GO
IF OBJECT_ID('dbo.test_DDL_log ') IS NOT NULL
DROP TABLE dbo.test_DDL_log;
GO
Results of this script show:
modify_date_BEFORE
-----------------------
2015-10-16 10:59:10.447
modify_date_AFTER
-----------------------
2015-10-16 10:59:15.487
id DDL_EventData dateCreated contextInfo
----------- ------------------------------------------------------------------------------------------------ ------------------------- ------------------------
1 <EVENT_INSTANCE><EventType>CREATE_TYPE</EventType><PostTime>2015-10-16T10:59:10.443</PostTime>....... 2015-10-16 10:59:10.443 Ticket 123 v1.2.345
2 <EVENT_INSTANCE><EventType>CREATE_FUNCTION</EventType><PostTime>2015-10-16T10:59:10.447</PostTime>... 2015-10-16 10:59:10.447 Ticket 123 v1.2.345
3 <EVENT_INSTANCE><EventType>RENAME</EventType><PostTime>2015-10-16T10:59:10.450</PostTime>............ 2015-10-16 10:59:10.450 Ticket 123 v1.2.345
4 <EVENT_INSTANCE><EventType>CREATE_TYPE</EventType><PostTime>2015-10-16T10:59:10.453</PostTime>....... 2015-10-16 10:59:10.453 Ticket 123 v1.2.345
The event for the sp_refreshsqlmodule
is nowhere to be found, and all logged events are prior to the sp_refreshsqlmodule
time of 2015-10-16 10:59:15.487 (note: I put a 5 second delay prior to the sp_refreshsqlmodule
call in the script above to make it more noticeable that this event was not being logged).