text: SQL - Decrypt
  if exists (select * from sysobjects where id = object_id('dbo.spDecryptSP') and sysstat & 0xf = 4)
      drop procedure dbo.spDecryptSP
  GO
  
  Create procedure dbo.spDecryptSP
      @spName varchar(50),
      @Replace bit = 0
  As
  /*
  Developer:    Jonathan Spinks
  Date: 24/07/03
  Description: Decrypts SQL 2000 stored procedures
  
  Inputs:
      @spName = the name of the stored procedure you wish to decrypt
      @Replace = 0 or 1. 0 if you wish to display the unencrypted sp
          1 if you wish to replace the encrypted with the unencrypted sp
  
  If this procedure returns "<@spName> is to large to decrypt"
  this stored procedure can be modified to decrypt it.
  Your starting point is to check out how many rows are returned
  in the syscomments table for the stored procedures object_id.
  
  If you just wish to display the sp then ensure that your result
  pane is turned to 'Results in Text'.
  In Query analyser (2k) Query => 'Results in Text'.
  
  If your returned information looks 'cut short' first check
  that your i_SQL query window is returning the entire string.
  In Query analyser (2k) Tools => Options, Results tab,
  Maximum characters per column = 8000.
  
  Happy decrypting
  
  Original idea: shoeboy <shoeboy@adequacy.org>
  Adapted by: Joseph Gama Copyright © 1999-2002 SecurityFocus
  Adapted Source: http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5
  Enhanced by: Jonathan Spinks Copyright © 2003 I.S. Software Developments. WASH
  */
  DECLARE
      @a1 nvarchar(4000), @b1 nvarchar(4000), @c1 nvarchar(4000), @d1 nvarchar(4000),
      @a2 nvarchar(4000), @b2 nvarchar(4000), @c2 nvarchar(4000), @d2 nvarchar(4000),
      @a3 nvarchar(4000), @b3 nvarchar(4000), @c3 nvarchar(4000), @d3 nvarchar(4000),
      @a4 nvarchar(4000), @b4 nvarchar(4000), @c4 nvarchar(4000), @d4 nvarchar(4000),
      @a5 nvarchar(4000), @b5 nvarchar(4000), @c5 nvarchar(4000), @d5 nvarchar(4000),
      @a6 nvarchar(4000), @b6 nvarchar(4000), @c6 nvarchar(4000), @d6 nvarchar(4000),
      @a7 nvarchar(4000), @b7 nvarchar(4000), @c7 nvarchar(4000), @d7 nvarchar(4000),
      @a8 nvarchar(4000), @b8 nvarchar(4000), @c8 nvarchar(4000), @d8 nvarchar(4000),
      @a9 nvarchar(4000), @b9 nvarchar(4000), @c9 nvarchar(4000), @d9 nvarchar(4000),
      @a0 nvarchar(4000), @b0 nvarchar(4000), @c0 nvarchar(4000), @d0 nvarchar(4000),
      @Perm nvarchar(4000),
      @i int
  
  if not exists(SELECT * FROM syscomments WHERE id = object_id(@spName))
  Begin
      print @spName +' cannot be found'
      return
  End
  
  if exists(SELECT * FROM syscomments WHERE id = object_id(@spName) and encrypted = 0)
  Begin
      print @spName +' is not encrypted'
      return
  End
  
  if (SELECT count(*) FROM syscomments WHERE id = object_id(@spName)) > 10
  Begin
      print @spName +' is to large to decrypt'
      return
  End
  
  --    Get a list of the current permissions on the encrypted stored procedure
  declare curPerm cursor fast_forward for
      select '['+ u.name +']' as name, p.actadd, p.actmod
      from    dbo.syspermissions p inner join dbo.sysusers u
          On p.grantee = u.uid
      where p.id = object_id(@spName)
  open curPerm
  
  Set @Perm = ''
  while 1 = 1
  begin
      declare @name sysname,
              @actadd smallint,
              @actmod smallint
      fetch next from curPerm into @name, @actadd, @actmod
      if @@fetch_status <> 0
          break
      --    For each permission in the list construct a GRANT or DENY command
      if @actadd & 32 = 32
          Set @Perm = @Perm +'GRANT EXECUTE ON '+ @spName +' TO '+ @name +char(13)+char(10)+'Go'+char(13)+char(10)
      else if @actmod & 32 = 32
          Set @Perm = @Perm +'DENY EXECUTE ON '+ @spName +' TO '+ @name +char(13)+char(10)+'Go'+char(13)+char(10)
  end
  close curPerm
  deallocate curPerm
  
  --    Get encrypted stored procedure
  SELECT @a1=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 1
  SELECT @a2=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 2
  SELECT @a3=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 3
  SELECT @a4=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 4
  SELECT @a5=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 5
  SELECT @a6=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 6
  SELECT @a7=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 7
  SELECT @a8=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 8
  SELECT @a9=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 9
  SELECT @a0=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 10
  
  --    Create blank stored procedure
  SET @b1='ALTER PROCEDURE '+ @spName +' WITH ENCRYPTION AS '+ REPLICATE('-', Len(@a1))
  SET @b2=REPLICATE(N'-', len(@a2))
  SET @b3=REPLICATE(N'-', len(@a3))
  SET @b4=REPLICATE(N'-', len(@a4))
  SET @b5=REPLICATE(N'-', len(@a5))
  SET @b6=REPLICATE(N'-', len(@a6))
  SET @b7=REPLICATE(N'-', len(@a7))
  SET @b8=REPLICATE(N'-', len(@a8))
  SET @b9=REPLICATE(N'-', len(@a9))
  SET @b0=REPLICATE(N'-', len(@a0))
  
  --    Wrap in transaction so original stored procedure can be restored
  Begin transaction ReplaceSP
  
      Execute (@b1 + @b2 + @b3 + @b4 + @b5 + @b6 + @b7 + @b8 + @b9 + @b0)
  
      --    Get blank encrypted stored procedure
      SELECT @c1=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 1
      SELECT @c2=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 2
      SELECT @c3=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 3
      SELECT @c4=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 4
      SELECT @c5=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 5
      SELECT @c6=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 6
      SELECT @c7=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 7
      SELECT @c8=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 8
      SELECT @c9=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 9
      SELECT @c0=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 10
  
  If @Replace = 0
      Rollback Transaction ReplaceSP
  Else
      Commit Transaction ReplaceSP
  
  SET @b1='CREATE PROCEDURE '+ @spName +' WITH ENCRYPTION AS '+ REPLICATE('-', Len(@a1))
  
  --    initalise the output variables
  Set @d1 = ''
  Set @d2 = ''
  Set @d3 = ''
  Set @d4 = ''
  Set @d5 = ''
  Set @d6 = ''
  Set @d7 = ''
  Set @d8 = ''
  Set @d9 = ''
  Set @d0 = ''
  
  --    Set the counter to one
  Set @i = 1
  WHILE @i < datalength(@a1)/2 + 1
  BEGIN
      --    (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
      SET @d1 = @d1 + NCHAR(UNICODE(substring(@a1, @i, 1)) ^ (UNICODE(substring(@b1, @i, 1)) ^ UNICODE(substring(@c1, @i, 1))))
      SET @i=@i+1
  END
  
  --    Set the counter to one
  Set @i = 1
  WHILE @i < datalength(@a2)/2 + 1
  BEGIN
      --    (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
      SET @d2 = @d2 + NCHAR(UNICODE(substring(@a2, @i, 1)) ^ (UNICODE(substring(@b2, @i, 1)) ^ UNICODE(substring(@c2, @i, 1))))
      SET @i=@i+1
  END
  
  --    Set the counter to one
  Set @i = 1
  WHILE @i < datalength(@a3)/2 + 1
  BEGIN
      --    (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
      SET @d3 = @d3 + NCHAR(UNICODE(substring(@a3, @i, 1)) ^ (UNICODE(substring(@b3, @i, 1)) ^ UNICODE(substring(@c3, @i, 1))))
      SET @i=@i+1
  END
  
  --    Set the counter to one
  Set @i = 1
  WHILE @i < datalength(@a4)/2 + 1
  BEGIN
      --    (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
      SET @d4 = @d4 + NCHAR(UNICODE(substring(@a4, @i, 1)) ^ (UNICODE(substring(@b4, @i, 1)) ^ UNICODE(substring(@c4, @i, 1))))
      SET @i=@i+1
  END
  
  --    Set the counter to one
  Set @i = 1
  WHILE @i < datalength(@a5)/2 + 1
  BEGIN
      --    (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
      SET @d5 = @d5 + NCHAR(UNICODE(substring(@a5, @i, 1)) ^ (UNICODE(substring(@b5, @i, 1)) ^ UNICODE(substring(@c5, @i, 1))))
      SET @i=@i+1
  END
  
  --    Set the counter to one
  Set @i = 1
  WHILE @i < datalength(@a6)/2 + 1
  BEGIN
      --    (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
      SET @d6 = @d6 + NCHAR(UNICODE(substring(@a6, @i, 1)) ^ (UNICODE(substring(@b6, @i, 1)) ^ UNICODE(substring(@c6, @i, 1))))
      SET @i=@i+1
  END
  
  --    Set the counter to one
  Set @i = 1
  WHILE @i < datalength(@a7)/2 + 1
  BEGIN
      --    (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
      SET @d7 = @d7 + NCHAR(UNICODE(substring(@a7, @i, 1)) ^ (UNICODE(substring(@b7, @i, 1)) ^ UNICODE(substring(@c7, @i, 1))))
      SET @i=@i+1
  END
  
  --    Set the counter to one
  Set @i = 1
  WHILE @i < datalength(@a8)/2 + 1
  BEGIN
      --    (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
      SET @d8 = @d8 + NCHAR(UNICODE(substring(@a8, @i, 1)) ^ (UNICODE(substring(@b8, @i, 1)) ^ UNICODE(substring(@c8, @i, 1))))
      SET @i=@i+1
  END
  
  --    Set the counter to one
  Set @i = 1
  WHILE @i < datalength(@a9)/2 + 1
  BEGIN
      --    (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
      SET @d9 = @d9 + NCHAR(UNICODE(substring(@a9, @i, 1)) ^ (UNICODE(substring(@b9, @i, 1)) ^ UNICODE(substring(@c9, @i, 1))))
      SET @i=@i+1
  END
  
  --    Set the counter to one
  Set @i = 1
  WHILE @i < datalength(@a0)/2 + 1
  BEGIN
      --    (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
      SET @d0 = @d0 + NCHAR(UNICODE(substring(@a0, @i, 1)) ^ (UNICODE(substring(@b0, @i, 1)) ^ UNICODE(substring(@c0, @i, 1))))
      SET @i=@i+1
  END
  
  
  if @Replace = 0
  Begin
      --    Output the unencrypted stored procedure to the screen
      select @d1 as 'Unencrypted Stored Procedure'
      select @d2
      select @d3
      select @d4
      select @d5
      select @d6
      select @d7
      select @d8
      select @d9
      select @d0
      --    Output any permissions that were on the encrypted stored procedure
      Select @Perm as 'Permissions'
  End
  Else
  Begin
      --    Drop the encrypted stored procedure
      Exec('Drop Procedure '+ @spName)
      --    Remove the 'WITH ENCRYPTION' command from the stored procedure
      Set @d1 = Replace(@d1, 'WITH ENCRYPTION', '')
      --    Create the unencrypted stored procedure
      Exec(@d1 + @d2 + @d3 + @d4 + @d5 + @d6 + @d7 + @d8 + @d9 + @d0)
      --    Apply any permissions that were on the encrypted stored procedure
      Exec(@Perm)
  End
  
  Go
  
  