A. Here is the code for SQL 6.5

  1. /************************************************/
  2. /* REVISED REC'D 5/21/98 */
  3. /* sp_decrypt_object Tom Sager 01/26/98 */
  4. /* */
  5. /* Decrypts objects (views, procedures & trigs) */
  6. /* created with the WITH ENCRYPTION option. */
  7. /* */
  8. /* Uses the encrypt() built-in function to find */
  9. /* a plaintext string that encrypts to the same */
  10. /* value as stored in the text column of the */
  11. /* syscomments table. */
  12. /* */
  13. /************************************************/
  14. CREATE proc sp_decrypt_object
  15. (@objname VARCHAR(30))
  16. WITH ENCRYPTION
  17. AS
  18.  
  19. SET NOCOUNT ON
  20.  
  21. DECLARE @errmsg VARCHAR(80)
  22. DECLARE @encrtext VARCHAR(255)
  23. DECLARE @decrtext VARCHAR(255)
  24. DECLARE @testtext VARCHAR(255)
  25. DECLARE @printline VARCHAR(255)
  26. DECLARE @textlen INT
  27. DECLARE @lup INT
  28. DECLARE @MATCH CHAR(1)
  29. DECLARE @testchar SMALLINT
  30. DECLARE @begblk SMALLINT
  31. DECLARE @endblk SMALLINT
  32.  
  33. IF (SELECT COUNT(*)
  34. FROM sysobjects
  35. WHERE name = @objname) = 0
  36. BEGIN
  37. SELECT @errmsg = 'Object '
  38. +@objname
  39. +' not found in database '
  40. +DB_NAME()
  41. print @errmsg
  42. RETURN 1
  43. END
  44.  
  45. IF (SELECT COUNT(*) FROM sysobjects t1,
  46. syscomments t2
  47. WHERE t1.name = @objname
  48. AND t1.id = t2.id
  49. AND t2.texttype & 4 != 0) = 0
  50. BEGIN
  51. SELECT @errmsg = 'Object '
  52. +@objname
  53. +' is not encrypted in database '
  54. +DB_NAME()
  55. print @errmsg
  56. RETURN 1
  57. END
  58.  
  59. DECLARE comments_cursor CURSOR FOR
  60. SELECT t1.text
  61. FROM syscomments t1,
  62. sysobjects t2
  63. WHERE t1.id = t2.id
  64. AND t2.name = @objname
  65. ORDER BY t1.colid
  66.  
  67. OPEN comments_cursor
  68.  
  69. FETCH NEXT FROM comments_cursor
  70. INTO @encrtext
  71.  
  72. WHILE (@@fetch_status <> -1)
  73. BEGIN
  74. IF (@@fetch_status <> -2)
  75. BEGIN
  76. SELECT @decrtext = REPLICATE(' ', 255)
  77. SELECT @textlen = DATALENGTH(@encrtext)
  78. SELECT @lup = 1
  79. SELECT @MATCH = 'n'
  80. while (@lup <= @textlen)
  81. BEGIN
  82. SELECT @testchar = 0
  83. SELECT @MATCH = 'n'
  84. while (@MATCH = 'n')
  85. BEGIN
  86. SELECT @decrtext =
  87. STUFF(@decrtext,@lup,1,CHAR(@testchar))
  88. SELECT @testtext = encrypt(@decrtext)
  89. IF ASCII(SUBSTRING(@testtext,@lup,1)) =
  90. ASCII(SUBSTRING(@encrtext,@lup,1))
  91. BEGIN
  92. SELECT @MATCH = 'y'
  93. END
  94. SELECT @testchar = @testchar + 1
  95. IF (@testchar > 255)
  96. BEGIN
  97. print 'Error...no match found'
  98. RETURN 1
  99. END
  100. END
  101. SELECT @lup = @lup + 1
  102. END
  103. SELECT @begblk = 1
  104. SELECT @endblk = 1
  105. while (@endblk <= @textlen)
  106. BEGIN
  107. IF (SUBSTRING(@decrtext,@endblk,1) = 0x0a)
  108. BEGIN
  109. SELECT @printline = @printline +
  110. SUBSTRING(@decrtext
  111. ,@begblk
  112. ,@endblk-@begblk+1)
  113. print @printline
  114. SELECT @begblk = @endblk + 1
  115. SELECT @endblk = @begblk
  116. SELECT @printline = NULL
  117. END
  118. SELECT @endblk = @endblk + 1
  119. END
  120. SELECT @printline = @printline +
  121. SUBSTRING(@decrtext
  122. ,@begblk
  123. ,@endblk-@begblk+1)
  124. END
  125. FETCH NEXT FROM comments_cursor INTO @encrtext
  126. END
  127. print @printline
  128. CLOSE comments_cursor
  129.  
  130. DEALLOCATE comments_cursor
  131. GO