Shulou(Shulou.com)06/02 Report--

SQL SERVER2008 stored procedures how to encrypt and decrypt, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

We often use encryption when setting up the database stored procedure, if we need to modify the stored procedure when we encounter a version update, if the stored procedure is not retained when designing the database, so we must use the decryption of the stored procedure. It turns out that there is no problem with the application of a decrypted stored procedure under SQL2000, but it is not easy to use after upgrading to SQL2008, so we will introduce the decryption method of the stored procedure under SQL2008.

Encryption of stored procedures

The method of stored procedure encryption is very simple, we can just add WITH ENCRYPTION when designing the stored procedure.

In this way, when you right-click the stored procedure in the database, the modify button is grayed out and cannot be viewed.

Next, let's talk about the decryption of SQL2008 stored procedures.

Decryption of stored procedure

Core process sp_DecryptObject

What we use for decryption is a stored procedure called sp_DecryptObject, which I will at least post its code. Here I will first talk about how to use this decryption method.

Create a decryption stored procedure

1. First open Microsoft SQL SERVER Managenment Studio and log in

two。 Then open the decrypted stored procedure, open it and press the execute key, and we create a stored procedure called sp_DecryptObject in the master inventory.

Note: there are two parameters in the above stored procedure. The first parameter is the name of the stored procedure we want to decrypt, and the second parameter is the character length to be decrypted. The default is 4000.

Decrypt using the stored procedure you created

1. The first step is to create a new database engine query. Click File-New-Database engine query.

Then add admin to our server name here:

Then select the database where our stored procedure is located in the drop-down box above and enter it in the text editor.

The name of the sp_DecryptObject stored procedure, and then press the execute key, and you can see that the result page below shows our stored procedure text.

Sp_Decrypttion parameter description

It is OK to copy it from the text and modify it after executing our stored procedure just now, but let's use a longer stored procedure to see the effect.

You can see that the above stored procedure text is not all displayed, which is because the second parameter defaults to 4000 when we create the stored procedure.

Let's re-modify the called stored procedure and enter the second parameter into a larger value.

Look at the effect.

We add a value of 50000 to the second parameter, and we can see that all the stored procedure code is displayed in the result of the execution.

Sp_Decryption source code

Use master

Go

If object_ID ('[sp_DecryptObject]') is not null

Drop Procedure [sp_DecryptObject]

Go

Create procedure sp_DecryptObject

(

@ Object sysname,-- the name of the object to be decrypted: function, stored procedure, view or trigger

@ MaxLength int=4000-evaluate the length of the content

)

As

Set nocount on

/ * 1. Decrypt * /

If not exists (select 1 from sys.objects a where a.object_id=object_id (@ Object) And a.type in)

Begin

-- SQL Server 2008

Raiserror 50001 N' invalid object! The object to be decrypted must be a function, stored procedure, view, or trigger.'

-- SQL Server 2012

-- throw 50001, N' invalid object! The object to be decrypted must be a function, stored procedure, view, or trigger.' , 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' object is not encrypted!'

-- SQL Server 2012

-- throw 50001, N' invalid object! The object to be decrypted must be a function, stored procedure, view, or trigger.' , 1

Return

End

Declare @ sql nvarchar (max)-- decrypted SQL statement

, @ imageval nvarchar (max)-- encrypted string

, @ tmpStr nvarchar (max)-- temporary SQL statement

, @ tmpStr_imageval nvarchar (max)-- temporary SQL statement (encrypted)

, @ type char (2)-- object type.

, @ objectID int-- object ID

, @ I int-- While recycle

, @ 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 constructs the following SQL statement

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 @ iTun0

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'' 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'-- identified as a system object

Go

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.