SQL Server decrypts stored procedures

    SQL Server Objectsencryption Method: Add "with Encryption" before the "as" position in the stored procedure, function, and "with encryption" in front of the "for" position, if it is a trigger.

Decryption process:

1. Execute the following script, Open the Administrator connection (DAC) to create the decryption stored procedure.

use master        go  -- enable dac       sp_configure  ' Show advanced options ', 1 go     sp_configure  ' Remote admin connections ', 1 go         RECONFIGURE WITH OVERRIDE    GOSELECT *   from sys.configurations where name =  ' Remote admin connections '--  create decrypt sp sp_decryptobjectif object_id (' [Sp_decryptobject] ')  is not  null    drop procedure [sp_decryptobject]gocreate procedure sp_ decryptobject  (     @Object  sysname,    --object name to decrypt: function, stored procedure, View or trigger      @MaxLength  int=4000 --Evaluate the length of the content) asset nocount on/* 1.   Decryption &NBSP;*/&NBSP;IF&NBSP;NOT&NBSp;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 ' Invalid Object! The object to decrypt must be a function, stored procedure, view, or trigger. '      --SQL Server 2012/2014    throw 50001,  N ' Invalid Object! The object to decrypt must be a function, stored procedure, view, or trigger. ', 1       returnend 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/2014    throw 50001,  N ' Invalid Object! The object to decrypt must be a function, stored procedure, view, or trigger. ', 1     returnend declare  @Sql nvarchar (max)                  --decrypted SQL statement         , @imageval  nvarchar (max)          --encrypted string         , @tmpStr  nvarchar (max)             --temporary SQL statements          , @tmpStr_imageval  nvarchar (max)  --temporary SQL statement (after encryption)    &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP, @type  char (2)                      --Object Type (' P ', ' V ', ' TR ', ' FN ', ' IF ', ' TF ')   &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP, @objectID  int                     --Object id         ,@i int                             --while Recycling        &NBSP;&NBSP, @Oject1  nvarchar (+)  set  @objectID =object_id (@Object) set  @type = (select  a.type from sys.objects a where [email protected])  declare @ Space4000 nchar (4000) set  @Space4000 =replicate ('-', 4000)  /* @tmpStr   The following SQL statement is constructed-------------------------------------------------------------------------------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  ' [email protected]+ '  with encryption as  select 1 as column1  '              when  @type  = ' v  '  then n ' alter view  ' [email protected]+ '  with encryption as select 1 as column1  '              when  @type  = ' FN '  then n ' alter  function  ' [email protected]+ ' ()  returns int with encryption as beGin return (0)  end  '             when   @type  = ' IF '  then n ' alter function  ' [email protected]+ ' ()  returns  table with encryption as return (Select a.name from sys.types a)   '             when  @type  = ' TF '  then n ' alter function  ' [email protected]+ ' ()  returns @t table (name  nvarchar ()  with encryption as begin return end  '              else  ' alter trigger  ' [email  protected]+ ' on  ' +quotename (Object_schema_name (@objectID)) + '. ' + (Select top (1)  quotename (object_name (parent_id))  from sys.triggers a where  [email protected]) + ' &NBSP;WITH&NBSP;ENCRYPTION&NBSP;FOR&NBSP;UPDATE&NBsp;as return  '          end              set @[email protected]+ '/* ' [email  protected]set @i=0while @i <  (Ceiling (@MaxLength *1.0/4000)-1) begin     set @[email protected]+  @Space4000     set @[email protected ]+1endset @[email protected]+ ' */'  ------------set  @imageval  = (select top (1)  a.imageval from sys.sysobjvalues a where [email protected] and  A.valclass=1) begin tranexec (@tmpStr) set  @tmpStr_imageval  = (select top (1)  a.imageval  from sys.sysobjvalues a where [email protected] and a.valclass=1)  rollback tran -------------set  @tmpStr =stuff (@tmpStr, 1,5, ' create ') set  @sql = ' Set  @i=1while @i<=  (DataleNgth (@imageval)/2) Begin    set @[email protected]+isnull (NCHAR (Unicode (substring (@tmpStr, @i,1))  ^ unicode (substring (@tmpStr_imageval, @i,1)) ^unicode (substring (@imageval, @i,1))  )      Set @i+=1end /* 2.  Printing  */  declare  @patindex  int     while  @sql > ' begin        set @ Patindex=patindex ('% ' +char (+) +char (Ten) + '% ', @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 (+ '% ', @sql)          if  @patindex  >0        begin             print substring (@sql, 1, @patindex-1)              set  @sql =stuff (@sql, 1, @patindex, ')         end        else         begin             set  @patindex =patindex ('% ' +char (Ten) + '% ', @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 goexec sys.sp_ms_ marksystemobject  ' Sp_decryptobject '  --identified as System object go

2. Open SSMs and create a new DAC connection, for example, with "admin:" before the instance name "Sqlclust\testal", click Connect:

3. run the following T-SQL statement to decrypt :

Use xxxxxgoexec sp_decryptobject ' sp_xxxxxxx ' GO

SQL Server decrypts stored procedures