G. 使用 WITH ENCRYPTION 选项
WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程,使用 sp_helptext 系统存储过程获取关于加密过程的信息,然后尝试直接从 syscomments 表中获取关于该过程的信息。IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GOEXEC sp_helptext encrypt_this下面是结果集:The object's comments have been encrypted.接下来,选择加密存储过程内容的标识号和文本。SELECT c.id, c.text
FROM syscomments c INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.name = 'encrypt_this'下面是结果集:说明 text 列的输出显示在单独一行中。执行时,该信息将与 id 列信息出现在同一行中。
id text
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????(1 row(s) affected)
WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程,使用 sp_helptext 系统存储过程获取关于加密过程的信息,然后尝试直接从 syscomments 表中获取关于该过程的信息。IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GOEXEC sp_helptext encrypt_this下面是结果集:The object's comments have been encrypted.接下来,选择加密存储过程内容的标识号和文本。SELECT c.id, c.text
FROM syscomments c INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.name = 'encrypt_this'下面是结果集:说明 text 列的输出显示在单独一行中。执行时,该信息将与 id 列信息出现在同一行中。
id text
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????(1 row(s) affected)
不过你可以使用Backup/Restore Database
declare @name varbinary(256),@test varchar(50)set @test='csdn'create table test (id int,name varbinary(256))insert into test values(1,pwdencrypt(@test))select @name = name from test where id=1if pwdcompare(@test,@name)=1print 'true'else
print 'false'drop table test
创建存储过程,存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。可以创建一个过程供永久使用,或在一个会话中临时使用(局部临时过程),或在所有会话中临时使用(全局临时过程)。也可以创建在 Microsoft® SQL Server™ 启动时自动运行的存储过程。语法
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ] [ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
使用 WITH ENCRYPTION 选项