decryption
-
hello once again,
this time i have encrypted a view and when i open its design view the error comes as :
"query designer encountered an unexpected error cant open encrypted view "
but the data in the view is seen, now i want to modify the design view but its not working any ideas have tried book online help but nothing is striking
thanks in advance
keyur
-
have not tested this code for decrypting encrypted objects - this should work for sql 7.0 ,it uses the decrypt function .pass the object name and the proc should print the decrypted definition , please tell me how you get along
create proc sp_decrypt_object
(@objname varchar(30))
WITH ENCRYPTION
as
SET NOCOUNT ON
declare @errmsg varchar(80)
declare @encrtext varchar(255)
declare @decrtext varchar(255)
declare @testtext varchar(255)
declare @printline varchar(255)
declare @textlen int
declare @lup int
declare @match char(1)
declare @testchar smallint
declare @decrlen smallint
declare @begblk smallint
declare @endblk smallint
if (select count(*)
from sysobjects
where name = @objname) = 0
begin
select @errmsg = 'Object '
+@objname
+' not found in database '
+DB_NAME()
print @errmsg
return 1
end
if (select count(*) from sysobjects t1,
syscomments t2
where t1.name = @objname
and t1.id = t2.id
and t2.texttype & 4 <> 0) = 0
begin
select @errmsg = 'Object '
+@objname
+' is not encrypted in database '
+DB_NAME()
print @errmsg
return 1
end
DECLARE comments_cursor CURSOR for
select t1.text
from syscomments t1,
sysobjects t2
where t1.id = t2.id
and t2.name = @objname
order by t1.colid
OPEN comments_cursor
FETCH NEXT FROM comments_cursor
INTO @encrtext
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
select @decrtext = REPLICATE(' ', 255)
select @textlen = DATALENGTH(@encrtext)
select @lup = 1
select @match = 'n'
while (@lup <= @textlen)
begin
select @testchar = 0
select @match = 'n'
while (@match = 'n')
begin
select @decrtext =
STUFF(@decrtext,@lup,1,CHAR(@testchar))
select @testtext = encrypt(@decrtext)
if (SUBSTRING(@testtext,@lup,1) =
SUBSTRING(@encrtext,@lup,1))
begin
select @match = 'y'
end
select @testchar = @testchar + 1
end
select @lup = @lup + 1
end
select @decrlen = datalength(@decrtext)
select @begblk = 1
select @endblk = 1
while (@endblk <= @decrlen)
begin
if (substring(@decrtext,@endblk,1) = 0x0a)
begin
select @printline = @printline +
SUBSTRING(@decrtext
,@begblk
,@endblk-@begblk+1)
print @printline
select @begblk = @endblk + 1
select @endblk = @begblk
select @printline = NULL
end
select @endblk = @endblk + 1
end
select @printline =
SUBSTRING(@decrtext
,@begblk
,@endblk-@begblk+1)
END
FETCH NEXT FROM comments_cursor INTO @encrtext
END
CLOSE comments_cursor
DEALLOCATE comments_cursor
go
Hope this helps
-
A script has been posted recently that lets you decrypt the statements used to build the view. Check the scripts area for details. I voted for it! (Even though it wasn't mine
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply