T-SQL????????????????????

???????????? ???????[ 2013/1/17 10:03:03 ] ????????

?????????????α????????????壬????????????????????

?????????????????????????????WITH ENCRYPTION???????????????????????塣???????????????????????????????????????????????????

????????????????????????????????????????????б??棬?????????????????

???????????????

--1????????????洢????
USE AdventureWorks
GO
CREATE PROC test
    WITH ENCRYPTION
AS
    SELECT SUSER_SNAME() ??
            USER_NAME()
GO
--2??????????????????????????????????EncryptByPassPhrase????????????????sys.sp_addextendedproperty?洢???????????????????
USE AdventureWorks
GO
DECLARE @sql VARCHAR(MAX)
SET @sql = 'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname()??user_name() GO'

--3?????????????????sql_variant????????
DECLARE @bsql SQL_VARIANT
SET @bsql = ( SELECT    CONVERT(SQL_VARIANT?? ENCRYPTBYPASSPHRASE('P@ssw0rd'??
                                                              CONVERT(VARCHAR(MAX)?? @sql)))
            )

--4????????????洢?????????????У?
EXEC sys.sp_addextendedproperty @name = N'test????'?? @value = N'System.Byte[]'??
    @level0type = N'SCHEMA'?? @level0name = N'dbo'?? @level1type = N'PROCEDURE'??
    @level1name = N'test'
GO
EXEC sys.sp_addextendedproperty @name = N'????????'??
    @value = N'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname()??user_name() GO'??
    @level0type = N'SCHEMA'?? @level0name = N'dbo'?? @level1type = N'PROCEDURE'??
    @level1name = N'test'
GO

--5?????
DECLARE @pwd VARCHAR(100)= 'P@ssw0rd'
--???????

DECLARE @proc VARCHAR(100)= 'test'
--?洢??????

DECLARE @exName NVARCHAR(100)= '????????'
--??????????

--???????????
SELECT 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   ( P.name = @exName )
        AND ( ( sp.type = N'p'
                OR sp.type = N'rf'
                OR sp.type = 'pc'
              )
              AND ( sp.name = @proc
                    AND SCHEMA_NAME(sp.schema_id) = N'dbo'
                  )
            )