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. SET NOCOUNT ON
  19. DECLARE @errmsg VARCHAR(80)
  20. DECLARE @encrtext VARCHAR(255)
  21. DECLARE @decrtext VARCHAR(255)
  22. DECLARE @testtext VARCHAR(255)
  23. DECLARE @printline VARCHAR(255)
  24. DECLARE @textlen INT
  25. DECLARE @lup INT
  26. DECLARE @MATCH CHAR(1)
  27. DECLARE @testchar SMALLINT
  28. DECLARE @begblk SMALLINT
  29. DECLARE @endblk SMALLINT
  30. IF (SELECT COUNT(*)
  31. FROM sysobjects
  32. WHERE name = @objname) = 0
  33. BEGIN
  34. SELECT @errmsg = 'Object '
  35. +@objname
  36. +' not found in database '
  37. +DB_NAME()
  38. print @errmsg
  39. RETURN 1
  40. END
  41. IF (SELECT COUNT(*) FROM sysobjects t1,
  42. syscomments t2
  43. WHERE t1.name = @objname
  44. AND t1.id = t2.id
  45. AND t2.texttype & 4 != 0) = 0
  46. BEGIN
  47. SELECT @errmsg = 'Object '
  48. +@objname
  49. +' is not encrypted in database '
  50. +DB_NAME()
  51. print @errmsg
  52. RETURN 1
  53. END
  54. DECLARE comments_cursor CURSOR FOR
  55. SELECT t1.text
  56. FROM syscomments t1,
  57. sysobjects t2
  58. WHERE t1.id = t2.id
  59. AND t2.name = @objname
  60. ORDER BY t1.colid
  61. OPEN comments_cursor
  62. FETCH NEXT FROM comments_cursor
  63. INTO @encrtext
  64. WHILE (@@fetch_status <> -1)
  65. BEGIN
  66. IF (@@fetch_status <> -2)
  67. BEGIN
  68. SELECT @decrtext = REPLICATE(' ', 255)
  69. SELECT @textlen = DATALENGTH(@encrtext)
  70. SELECT @lup = 1
  71. SELECT @MATCH = 'n'
  72. while (@lup <= @textlen)
  73. BEGIN
  74. SELECT @testchar = 0
  75. SELECT @MATCH = 'n'
  76. while (@MATCH = 'n')
  77. BEGIN
  78. SELECT @decrtext =
  79. STUFF(@decrtext,@lup,1,CHAR(@testchar))
  80. SELECT @testtext = encrypt(@decrtext)
  81. IF ASCII(SUBSTRING(@testtext,@lup,1)) =
  82. ASCII(SUBSTRING(@encrtext,@lup,1))
  83. BEGIN
  84. SELECT @MATCH = 'y'
  85. END
  86. SELECT @testchar = @testchar + 1
  87. IF (@testchar > 255)
  88. BEGIN
  89. print 'Error...no match found'
  90. RETURN 1
  91. END
  92. END
  93. SELECT @lup = @lup + 1
  94. END
  95. SELECT @begblk = 1
  96. SELECT @endblk = 1
  97. while (@endblk <= @textlen)
  98. BEGIN
  99. IF (SUBSTRING(@decrtext,@endblk,1) = 0x0a)
  100. BEGIN
  101. SELECT @printline = @printline +
  102. SUBSTRING(@decrtext
  103. ,@begblk
  104. ,@endblk-@begblk+1)
  105. print @printline
  106. SELECT @begblk = @endblk + 1
  107. SELECT @endblk = @begblk
  108. SELECT @printline = NULL
  109. END
  110. SELECT @endblk = @endblk + 1
  111. END
  112. SELECT @printline = @printline +
  113. SUBSTRING(@decrtext
  114. ,@begblk
  115. ,@endblk-@begblk+1)
  116. END
  117. FETCH NEXT FROM comments_cursor INTO @encrtext
  118. END
  119. print @printline
  120. CLOSE comments_cursor
  121. DEALLOCATE comments_cursor
  122. GO