《sqlServer存储过程的加密和解密方法.doc》由会员分享，可在线阅读，更多相关《sqlServer存储过程的加密和解密方法.doc（6页珍藏版）》请在三一文库上搜索。
1、sql 存储过程的加密和解密加密存储过程：IF EXISTS (SELECT name FROM sysobjects WHERE name = encrypt_this AND type = P) DROP PROCEDURE encrypt_thisGOUSE pubsGOCREATE PROCEDURE encrypt_thisWITH ENCRYPTION -只需要在创建时加入 WITH ENCRYPTIO即可ASSELECT * FROM authorsGO- 查看存储过程文本：EXEC sp_helptext encrypt_this下面是结果集：The objects comme
2、nts have been encrypted. 对象已经被加密解密存储过程：CREATE PROCEDURE sp_decrypt(objectName varchar(50)ASbeginset nocount on-CSDN： j9988 copyright:2004.04.15-V3.1-破解字节不受限制，适用于SQLSERVER20C存储过程，函数，视图，触发器- 修正上一版视图触发器不能正确解密错误- 发现有错，请 E_MAIL：CSDNj9988 begin trandeclare objectname1 varchar(100),orgvarbin varbinary(8000
3、)declare sql1 nvarchar(4000),sql2 varchar(8000),sql3 nvarchar(4000),sql4 nvarchar(4000)DECLARE OrigSpText1 nvarchar(4000), OrigSpText2nvarchar(4000) , OrigSpText3nvarchar(4000), resultsp nvarchar(4000)declare i int,status int,type varchar(10),parentid intdeclare colid int,n int,q int,j int,k int,enc
4、rypted int,number intselect type=xtype,parentid=parent_obj from sysobjects where id=object_id(ObjectName)create table #temp(number int,colid int,ctext varbinary(8000),encrypted int,status int)insert #temp SELECTnumber,colid,ctext,encrypted,status FROMsyscomments WHEREid = object_id(objectName)select
5、 number=max(number) from #tempset k=0while k1 then ALTER PROCEDURE + objectName +;+rtrim(k)+ WITH ENCRYPTION AS else ALTER PROCEDURE + objectName+ WITH ENCRYPTIONAS end)if type=TRbegindeclare parent_obj varchar(255),tr_parent_xtype varchar(10)select parent_obj=parent_obj from sysobjects where id=obj
6、ect_id(objectName)select tr_parent_xtype=xtype from sysobjects where id=parent_objif tr_parent_xtype=Vbeginset sql1=ALTER TRIGGER +objectname+ ON +OBJECT_NAME(parentid)+ WITH ENCRYPTION INSTERD OF INSERT AS PRINT 1 end else beginset sql1=ALTER TRIGGER +objectname+ ON +OBJECT_NAME(parentid)+ WITH ENC
7、RYPTION FOR INSERT AS PRINT 1 endendif type=FN or type=TF or type=IFset sql1=(case type when TF thenALTER FUNCTION + objectName+(a char(1) returns b table(a varchar(10) with encryption as begin insert b select a return end when FN thenALTER FUNCTION + objectName+(a char(1) returns char(1) with encry
8、ption as begin return a end when IF thenALTER FUNCTION+ objectName+(a char(1) returns table with encryption as return select a as aend)if type=Vset sql1=ALTER VIEW +objectname+ WITH ENCRYPTION AS SELECT 1 as fset q=len(sql1)set sql1=sql1+REPLICATE(-,4000-q)select sql2=REPLICATE(-,8000)set sql3=exec(
9、sql1select colid=max(colid) from #temp where number=kset n=1while n=CEILING(1.0*(colid-1)/2) and len(sQL3)=3996beginset sql3=sql3+set n=n+1endset sql3=sql3+)exec sp_executesql sql3,NSql1 nvarchar(4000), varchar(8000),sql1=sql1,=sql2endset k=k+1endset k=0while k=number beginif exists(select 1 from sy
10、scomments where id=object_id(objectname) and number=k) beginselect colid=max(colid) from #temp where number=kset n=1 while n1 then CREATE PROCEDURE + objectName +;+rtrim(k)+ WITH ENCRYPTION AS else CREATE PROCEDUR+E objectName + WITHENCRYPTIONASIend)if type=FN or type=TF or type=IFSET OrigSpText2=(c
11、ase type when TF thenCREATE FUNCTION+ objectName+(a char(1) returns btable(a varchar(10) with encryption as begin insert b select a return end when FN thenCREATE FUNCTION + objectName+(a char(1) returns char(1) with encryption as begin return a endwhen IF thenCREATE FUNCTION + objectName+(a char(1)
12、returns table with encryption as return select a as aend)if type=TRbeginif tr_parent_xtype=Vbeginset OrigSpText2=CREATE TRIGGER +objectname+ ON +OBJECT_NAME(parentid)+ WITH ENCRYPTION INSTEAD OF INSERT AS PRINT 1 endelsebeginset OrigSpText2=CREATE TRIGGER +objectname+ ON +OBJECT_NAME(parentid)+ WITH
13、 ENCRYPTION FOR INSERT AS PRINT 1 endif type=Vset OrigSpText2=CREATE VIEW +objectname+ WITH ENCRYPTION AS SELECT 1 as fset q=4000-len(OrigSpText2)set OrigSpText2=OrigSpText2+REPLICATE(-,q)endelsebeginSET OrigSpText2=REPLICATE(-, 4000)endSET i=1SET resultsp = replicate(NA, (datalength(OrigSpText1) /
14、2)WHILE i=datalength(OrigSpText1)/2BEGINi,SET resultsp = stuff(resultsp, i, 1, NCHAR(UNICODE(substring(OrigSpText1, 1) A(UNICODE(substri ng(0rigSpText2, i, 1) a UNICODE(substring(OrigSpText3, i, 1)SET i=i+1ENDset orgvarbin=cast(OrigSpText1 as varbinary(8000)set resultsp=(case when encrypted=1then re
15、sultspelse convert(nvarchar(4000),case when status&2=2 then uncompress(orgvarbin) else orgvarbin end)end)print resultspset n=n+1endendset k=k+1enddrop table #temprollback tranend 创建存储过程 sp_decrypt 后，执行以下操作 .调用时， exec sp_decrypt 要解密的存储过程名- 此时 使用 exec sp_helptext 存储过程名，即可查看存储过程的文本信息 本文参考 sqlserver 联机丛书以及在网上整理。