1.視圖 存儲過程 觸發器 批量加密（With Encryption），單個解密?
在運行過程中自己找不到啟用DAC 的地方，鏈接的時候需要在服務器名稱前面添加ADMIN:，如本機是ADMIN:WP-PC?
另外加密后的對象可以用ＳＱＬ ｐｒｏｍｐｔ 直接單獨查看，所以意義不是很大；防一些不懂的人吧，親測可以使用；
網絡上面有2000的，我自己測試在2008R2，作者本人在2012上面測試也是ＯＫ的
--加密存儲過程? 可以批量加密參數All 或者單個加密? 已經加密的會有提示
Use
master
Go
if
object_ID
(
'
[sp_EncryptObject]
'
)
is
not
null
Drop
Procedure
[
sp_EncryptObject
]
Go
create
procedure
sp_EncryptObject
(
@Object
sysname
=
'
All
'
)
as
/*
當@Object=All的時候，對所有的函數，存儲過程，視圖和觸發器進行加密
調用方法：
1. Execute sp_EncryptObject 'All'
2. Execute sp_EncryptObject 'ObjectName'
*/
begin
set
nocount
on
if
@Object
<>
'
All
'
begin
if
not
exists
(
select
1
from
sys.objects a
where
a.
object_id
=
object_id
(
@Object
)
And
a.type
in
(
'
P
'
,
'
V
'
,
'
TR
'
,
'
FN
'
,
'
IF
'
,
'
TF
'
))
begin
--
SQL Server 2008
raiserror
50001
N
'
無效的加密對象！加密對象必須是函數，存儲過程，視圖或觸發器。
'
--
SQL Server 2012
--
throw 50001, N'無效的加密對象！加密對象必須是函數，存儲過程，視圖或觸發器。',1
return
end
if
exists
(
select
1
from
sys.sql_modules a
where
a.
object_id
=
object_id
(
@Object
)
and
a.definition
is
null
)
begin
--
SQL Server 2008
raiserror
50001
N
'
對象已經加密！
'
--
SQL Server 2012
--
throw 50001, N'對象已經加密！',1
return
end
end
declare
@sql
nvarchar
(
max
),
@C1
nchar
(
1
),
@C2
nchar
(
1
),
@type
nvarchar
(
50
),
@Replace
nvarchar
(
50
)
set
@C1
=
nchar
(
13
)
set
@C2
=
nchar
(
10
)
declare
cur_Object
cursor
for
select
object_name
(a.
object_id
)
As
ObjectName,a.definition
from
sys.sql_modules a
inner
join
sys.objects b
on
b.
object_id
=
a.
object_id
and
b.is_ms_shipped
=
0
and
not
exists
(
select
1
from
sys.extended_properties x
where
x.major_id
=
b.
object_id
and
x.minor_id
=
0
and
x.class
=
1
and
x.name
=
'
microsoft_database_tools_support
'
)
where
b.type
in
(
'
P
'
,
'
V
'
,
'
TR
'
,
'
FN
'
,
'
IF
'
,
'
TF
'
)
and
(b.name
=
@Object
or
@Object
=
'
All
'
)
and
b.name
<>
'
sp_EncryptObject
'
and
a.definition
is
not
null
order
by
Case
when
b.type
=
'
V
'
then
1
when
b.type
=
'
TR
'
then
2
when
b.type
in
(
'
FN
'
,
'
IF
'
,
'
TF
'
)
then
3
else
4
end
,b.create_date,b.
object_id
open
cur_Object
fetch
next
from
cur_Object
into
@Object
,
@sql
while
@@fetch_status
=
0
begin
Begin
Try
if
objectproperty
(
object_id
(
@Object
),
'
ExecIsAfterTrigger
'
)
=
0
set
@Replace
=
'
As
'
;
else
set
@Replace
=
'
For
'
;
if
(
patindex
(
'
%
'
+
@C1
+
@C2
+
@Replace
+
@C1
+
@C2
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C1
+
@C2
+
@Replace
+
@C1
+
@C2
,
@C1
+
@C2
+
'
With Encryption
'
+
@C1
+
@C2
+
@Replace
+
@C1
+
@C2
)
end
else
if
(
patindex
(
'
%
'
+
@C1
+
@Replace
+
@C1
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C1
+
@Replace
+
@C1
,
@C1
+
'
With Encryption
'
+
@C1
+
@Replace
+
@C1
)
end
else
if
(
patindex
(
'
%
'
+
@C2
+
@Replace
+
@C2
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C2
+
@Replace
+
@C2
,
@C2
+
'
With Encryption
'
+
@C2
+
@Replace
+
@C2
)
end
else
if
(
patindex
(
'
%
'
+
@C2
+
@Replace
+
@C1
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C2
+
@Replace
+
@C1
,
@C1
+
'
With Encryption
'
+
@C2
+
@Replace
+
@C1
)
end
else
if
(
patindex
(
'
%
'
+
@C1
+
@C2
+
@Replace
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C1
+
@C2
+
@Replace
,
@C1
+
@C2
+
'
With Encryption
'
+
@C1
+
@C2
+
@Replace
)
end
else
if
(
patindex
(
'
%
'
+
@C1
+
@Replace
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C1
+
@Replace
,
@C1
+
'
With Encryption
'
+
@C1
+
@Replace
)
end
else
if
(
patindex
(
'
%
'
+
@C2
+
@Replace
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C2
+
@Replace
,
@C2
+
'
With Encryption
'
+
@C2
+
@Replace
)
end
set
@type
=
case
when
object_id
(
@Object
,
'
P
'
)
>
0
then
'
Proc
'
when
object_id
(
@Object
,
'
V
'
)
>
0
then
'
View
'
when
object_id
(
@Object
,
'
TR
'
)
>
0
then
'
Trigger
'
when
object_id
(
@Object
,
'
FN
'
)
>
0
or
object_id
(
@Object
,
'
IF
'
)
>
0
or
object_id
(
@Object
,
'
TF
'
)
>
0
then
'
Function
'
end
set
@sql
=
Replace
(
@sql
,
'
Create
'
+
@type
,
'
Alter
'
+
@type
)
Begin
Transaction
exec
(
@sql
)
print
N
'
已完成加密對象(
'
+
@type
+
'
)：
'
+
@Object
Commit
Transaction
End
Try
Begin
Catch
Declare
@Error
nvarchar
(
2047
)
Set
@Error
=
'
Object:
'
+
@Object
+
@C1
+
@C2
+
'
Error:
'
+
Error_message()
Rollback
Transaction
print
@Error
print
@sql
End
Catch
fetch
next
from
cur_Object
into
@Object
,
@sql
end
close
cur_Object
deallocate
cur_Object
end
Go
exec
sp_ms_marksystemobject
'
sp_EncryptObject
'
--
標識為系統對象
go
--解密存儲過程
Use
master
Go
if
object_ID
(
'
[sp_DecryptObject]
'
)
is
not
null
Drop
Procedure
[
sp_DecryptObject
]
Go
create
procedure
sp_DecryptObject
(
@Object
sysname,
--
要解密的對象名：函數，存儲過程，視圖或觸發器
@MaxLength
int
=
4000
--
評估內容的長度
)
as
set
nocount
on
/*
1. 解密
*/
if
not
exists
(
select
1
from
sys.objects a
where
a.
object_id
=
object_id
(
@Object
)
And
a.type
in
(
'
P
'
,
'
V
'
,
'
TR
'
,
'
FN
'
,
'
IF
'
,
'
TF
'
))
begin
--
SQL Server 2008
raiserror
50001
N
'
無效的對象！要解密的對象必須是函數，存儲過程，視圖或觸發器。
'
--
SQL Server 2012
--
throw 50001, N'無效的對象！要解密的對象必須是函數，存儲過程，視圖或觸發器。',1
return
end
if
exists
(
select
1
from
sys.sql_modules a
where
a.
object_id
=
object_id
(
@Object
)
and
a.definition
is
not
null
)
begin
--
SQL Server 2008
raiserror
50001
N
'
對象沒有加密！
'
--
SQL Server 2012
--
throw 50001, N'無效的對象！要解密的對象必須是函數，存儲過程，視圖或觸發器。',1
return
end
declare
@sql
nvarchar
(
max
)
--
解密出來的SQL語句
,
@imageval
nvarchar
(
max
)
--
加密字符串
,
@tmpStr
nvarchar
(
max
)
--
臨時SQL語句
,
@tmpStr_imageval
nvarchar
(
max
)
--
臨時SQL語句(加密后)
,
@type
char
(
2
)
--
對象類型('P','V','TR','FN','IF','TF')
,
@objectID
int
--
對象ID
,
@i
int
--
While循環使用
,
@Oject1
nvarchar
(
1000
)
set
@objectID
=
object_id
(
@Object
)
set
@type
=
(
select
a.type
from
sys.objects a
where
a.
object_id
=
@objectID
)
declare
@Space4000
nchar
(
4000
)
set
@Space4000
=
replicate
(
'
-
'
,
4000
)
/*
@tmpStr 會構造下面的SQL語句
-------------------------------------------------------------------------------
alter trigger Tr_Name on Table_Name with encryption for update as return /*
*/
alter
proc
Proc_Name
with
encryption
as
select
1
as
col
/**/
alter
view
View_Name
with
encryption
as
select
1
as
col
/**/
alter
function
Fn_Name()
returns
int
with
encryption
as
begin
return
(
0
)
end
/**/
*/
set
@Oject1
=
quotename
(object_schema_name(
@objectID
))
+
'
.
'
+
quotename
(
@Object
)
set
@tmpStr
=
case
when
@type
=
'
P
'
then
N
'
Alter Procedure
'
+
@Oject1
+
'
with encryption as select 1 as column1
'
when
@type
=
'
V
'
then
N
'
Alter View
'
+
@Oject1
+
'
with encryption as select 1 as column1
'
when
@type
=
'
FN
'
then
N
'
Alter Function
'
+
@Oject1
+
'
() returns int with encryption as begin return(0) end
'
when
@type
=
'
IF
'
then
N
'
Alter Function
'
+
@Oject1
+
'
() returns table with encryption as return(Select a.name from sys.types a)
'
when
@type
=
'
TF
'
then
N
'
Alter Function
'
+
@Oject1
+
'
() returns @t table(name nvarchar(50)) with encryption as begin return end
'
else
'
Alter Trigger
'
+
@Oject1
+
'
on
'
+
quotename
(object_schema_name(
@objectID
))
+
'
.
'
+
(
select
Top
(
1
)
quotename
(
object_name
(parent_id))
from
sys.triggers a
where
a.
object_id
=
@objectID
)
+
'
with encryption for update as return
'
end
set
@tmpStr
=
@tmpStr
+
'
/*
'
+
@Space4000
set
@i
=
0
while
@i
<
(
ceiling
(
@MaxLength
*
1.0
/
4000
)
-
1
)
begin
set
@tmpStr
=
@tmpStr
+
@Space4000
Set
@i
=
@i
+
1
end
set
@tmpStr
=
@tmpStr
+
'
*/
'
--
----------
set
@imageval
=
(
select
top
(
1
) a.imageval
from
sys.sysobjvalues a
where
a.objid
=
@objectID
and
a.valclass
=
1
)
begin
tran
exec
(
@tmpStr
)
set
@tmpStr_imageval
=
(
select
top
(
1
) a.imageval
from
sys.sysobjvalues a
where
a.objid
=
@objectID
and
a.valclass
=
1
)
rollback
tran
--
-----------
set
@tmpStr
=
stuff
(
@tmpStr
,
1
,
5
,
'
create
'
)
set
@sql
=
''
set
@i
=
1
while
@i
<=
(
datalength
(
@imageval
)
/
2
)
begin
set
@sql
=
@sql
+
isnull
(
nchar
(
unicode
(
substring
(
@tmpStr
,
@i
,
1
))
^
unicode
(
substring
(
@tmpStr_imageval
,
@i
,
1
))
^
unicode
(
substring
(
@imageval
,
@i
,
1
)) ),
''
)
Set
@i
+=
1
end
/*
2. 列印
*/
declare
@patindex
int
while
@sql
>
''
begin
set
@patindex
=
patindex
(
'
%
'
+
char
(
13
)
+
char
(
10
)
+
'
%
'
,
@sql
)
if
@patindex
>
0
begin
print
substring
(
@sql
,
1
,
@patindex
-
1
)
set
@sql
=
stuff
(
@sql
,
1
,
@patindex
+
1
,
''
)
end
else
begin
set
@patindex
=
patindex
(
'
%
'
+
char
(
13
)
+
'
%
'
,
@sql
)
if
@patindex
>
0
begin
print
substring
(
@sql
,
1
,
@patindex
-
1
)
set
@sql
=
stuff
(
@sql
,
1
,
@patindex
,
''
)
end
else
begin
set
@patindex
=
patindex
(
'
%
'
+
char
(
10
)
+
'
%
'
,
@sql
)
if
@patindex
>
0
begin
print
substring
(
@sql
,
1
,
@patindex
-
1
)
set
@sql
=
stuff
(
@sql
,
1
,
@patindex
,
''
)
end
else
begin
print
@sql
set
@sql
=
''
end
end
end
end
Go
exec
sp_ms_marksystemobject
'
sp_DecryptObject
'
--
標識為系統對象
go
--解密測試
CREATE
PROC
sp_SplitResult2
With
Encryption
As
BEGIN
SELECT
*
FROM
dbo.Orders
END
exec
sp_DecryptObject sp_SplitResult2
http://www.cnblogs.com/lyhabc/p/3384906.html
http://www.cnblogs.com/wghao/archive/2012/12/30/2837642.html
下面的是利用工具批量解密 網址 都是華仔的
http://www.cnblogs.com/lyhabc/p/3505677.html
2.
以游標技術，列舉出所有學生的名單，包括學生姓名、選擇的課程的數量，SQL題目
-- 前面先取一次數據，后面再調用賦值的變量 在循環里面利用賦值的變量 去到課程表里面找所選課程數量
--假設有2個表?tStudent(sno,name?)??tCourse(sno?CourseName)
--
假設有2個表 tStudent(sno,name ) tCourse(sno CourseName)
IF
OBJECT_ID
(
'
tStudent
'
)
>
0
DROP
TABLE
tStudent
IF
OBJECT_ID
(
'
tCourse
'
)
>
0
DROP
TABLE
tCourse
CREATE
TABLE
tStudent
(
sno
VARCHAR
(
10
) ,
name
NVARCHAR
(
10
)
)
CREATE
TABLE
tCourse
(
sno
VARCHAR
(
10
) ,
CourseName
NVARCHAR
(
10
)
)
INSERT
dbo.tStudent
( sno, name )
VALUES
(
'
001
'
,
--
fstudentno - varchar(10)
N
'
小張
'
--
fname - nvarchar(10)
)
INSERT
dbo.tStudent
( sno, name )
VALUES
(
'
002
'
,
--
fstudentno - varchar(10)
N
'
小李
'
--
fname - nvarchar(10)
)
INSERT
dbo.tStudent
( sno, name )
VALUES
(
'
003
'
,
--
fstudentno - varchar(10)
N
'
小如
'
--
fname - nvarchar(10)
)
INSERT
dbo.tCourse
( sno, CourseName )
VALUES
(
'
001
'
,
--
sno - varchar(10)
N
'
英語
'
--
CourseName - nvarchar(10)
)
INSERT
dbo.tCourse
( sno, CourseName )
VALUES
(
'
001
'
,
--
sno - varchar(10)
N
'
語文
'
--
CourseName - nvarchar(10)
)
INSERT
dbo.tCourse
( sno, CourseName )
VALUES
(
'
002
'
,
--
sno - varchar(10)
N
'
語文
'
--
CourseName - nvarchar(10)
)
--建立存儲過程 里面使用游標遍歷所有學生
Create
PROC
GetInfo
AS
BEGIN
DECLARE
curName
CURSOR
FAST_FORWARD
FOR
(
SELECT
DISTINCT
*
FROM
dbo.tStudent
)
OPEN
curName
DECLARE
@sno
VARCHAR
(
10
) ,
@name
NVARCHAR
(
10
) ,
@coursenum
INT
DECLARE
@tb
TABLE
(
name
NVARCHAR
(
10
) ,
coursenum
INT
)
FETCH
NEXT
FROM
curName
INTO
@sno
,
@name
SELECT
@coursenum
=
ISNULL
(
COUNT
(
DISTINCT
CourseName),
0
)
FROM
tCourse
WHERE
sno
=
@sno
--
INSERT @tb
--
SELECT @name ,
--
@coursenum
WHILE
@@FETCH_STATUS
=
0
BEGIN
SELECT
@coursenum
=
ISNULL
(
COUNT
(
DISTINCT
CourseName),
0
)
FROM
tCourse
WHERE
sno
=
@sno
INSERT
@tb
SELECT
@name
,
@coursenum
FETCH
NEXT
FROM
curName
INTO
@sno
,
@name
END
SELECT
*
FROM
@tb
CLOSE
curName
DEALLOCATE
curName
END
--? 查看執行結果
?exec GetInfo
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
