Sign a SQL Stored Procedure to track changes.

Several times I've run into situations where I need to deploy a SQL stored procedure to clients that maybe complex and critical to correct functioning of the database.

Things go along fine and then I get a call/Email after about 6 months with a bug or performance issue. After a little detective work I think I detect that a critical procedure as been changed either by the client or consultant working on their site. Verifying that the stored procedure is the orginal unaltered version I placed on the server can lead to some long conversations.

So I came up with a solution that allows me to "sign" or finger print any SP after I've installed it. The technique uses SQL 2005 and above ability to hash data with the HashBytes function. The hash contains date information, object definition, object IDs, and a "secret" word.

The secret word allows you to publish the signing procedures without fear that the end-user will fake a signature. At least as long as you don't tell them the secret word.

The resulting binary hash signature is stored in the extended properties of the procedure and cannot be modified using SSMS.

I've found this very helpful with some of my clients that love to tinker with critical procs.

Dave Winters
Terminus
--------------------------------------------------------------------------
--
--
if object_id('USP_Sign_Object') is not NULL
drop procedure USP_Sign_Object
GO
--------------------------------------------------------------
create procedure USP_Sign_Object
@objectName sysname
,@secretWord nvarchar(20)='My Software'
as
--
-- This procedure builds a hash of the contents of a stored procedure for "finger printing"
-- and detecting changes. The hash/signature is stored in the extended properties of the object.
-- Here are some implementatio details:
-- A "secret" word should be supplied to prevent spoofing.
-- The has includes the object ID, create date, modify date, and first 4000 bytes of the DDL
-- To resign the procedure, the "SIGNED" property must be dropped via SSMS
--
-- Dave Winters, January 2010
-- TheSeldonVault.blogspot.com
--
SET NOCOUNT ON
Begin --Top

declare @objid nvarchar(20),
@crdate nvarchar(20),
@modate nvarchar(20),
@signHash sql_variant

select @objid=cast(object_id as nvarchar(20))
,@crdate=cast(create_date as nvarchar(20))
,@modate=cast(modify_date as nvarchar(20)) from sys.objects where [name]=@objectName

if @@rowcount = 0 return -1

SELECT @signHash = cast( HashBytes(
'SHA1',isnull(substring(
@secretWord+@objid+@crdate+@modate
+OBJECT_DEFINITION(OBJECT_ID (@objectName))
,1,4000 ),'EnCrYpTeD' )
) as sql_variant)

EXEC sys.sp_addextendedproperty @name=N'SIGNED'
,@value=@signHash
,@level0type=N'SCHEMA'
,@level0name=N'dbo'
,@level1type=N'PROCEDURE'
,@level1name=@objectName

End --Bottom
GO
--
--exec USP_Sign_Object 'asp_benefit_warnings','MySeCrEtE'
--
------------------------------------------------------------------------
if object_id('USP_Validate_Object') is not NULL
drop procedure USP_Validate_Object
GO
--
create procedure USP_Validate_Object
@objectName sysname
,@secretWord nvarchar(20)='My Software'
as
SET NOCOUNT ON
Begin --Top

declare @objid nvarchar(20),
@crdate nvarchar(20),
@modate nvarchar(20),
@signHash sql_variant,
@currHash sql_variant

select @objid=cast(object_id as nvarchar(20))
,@crdate=cast(create_date as nvarchar(20))
,@modate=cast(modify_date as nvarchar(20)) from sys.objects where [name]=@objectName

if @@rowcount = 0 return -1

SELECT @signHash = cast( HashBytes('SHA1',isnull(substring(
@secretWord+@objid+@crdate+@modate
+OBJECT_DEFINITION(OBJECT_ID (@objectName))
,1,4000 ),'EnCrYpTeD' )
)
as sql_variant)
--
SELECT @currHash = p.value
FROM sys.all_objects AS sp
INNER JOIN sys.extended_properties AS p ON p.major_id=sp.object_id AND p.minor_id=0 AND p.class=1
where sp.name = @objectName--N'asp_benefit_warnings'
and p.name = 'SIGNED'

--select @currHash as CurrHash, @signHash as CompHash

if @currHash <> @signHash
begin
print '!!!Signature Invalid!!!'
return -1
end

Print '---Signature Valid---'
return 0

End --Bottom

--
--
--exec USP_Validate_Object 'asp_benefit_warnings','MySeCrEtE'
--
--