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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply