sp_configure 'remote admin connections', 1 ;
GO
RECONFIGURE ;
GO
After that, you need to disconnect ONLY YOUR QUERY window, not object explorer. Object explorer will not connect as remote admin. Also, you will kick out any existing connections to your database when you connect as remote admin, so do not do this on a production box. To connect as remote admin, connect your query window again, but type "ADMIN:" before the server name (minus the quotes, of course), and connect using sa or an administrator account.
Then, you can edit the <catalog>, <schema>, and <object> areas (one of each) in the following query to decrypt a stored procedure.
I take absolutely NO credit for writing this, I found it on a website, and I don't remember where, but there were no credits in it where I found it.
USE [<catalog>]
GO
DECLARE @procedure sysname
SELECT@procedure = '<schema>.<object>'
DECLARE @intProcSpace bigint
DECLARE @t bigint
DECLARE @maxColID smallint
DECLARE @intEncrypted tinyint
DECLARE @procNameLength int
DECLARE @real_01 nvarchar(max)
DECLARE @fake_01 nvarchar(max)
DECLARE @fake_encrypt_01 nvarchar(max)
DECLARE @real_decrypt_01 nvarchar(max)
DECLARE @real_decrypt_01a nvarchar(max)
create table #output
(
[ident] [int] IDENTITY(1, 1)
NOT NULL
,[real_decrypt] NVARCHAR(MAX)
)
SELECT
@maxColID = max(subobjid)
FROM
sys.sysobjvalues
WHERE
objid = object_id(@procedure)
select
@procNameLength = datalength(@procedure) + 29
select
@real_decrypt_01a = ''
SET @real_01 = (
SELECT
imageval
FROM
sys.sysobjvalues
WHERE
objid = object_id(@procedure)
and valclass = 1
and subobjid = 1
)
BEGIN TRAN
SET @fake_01 = 'ALTER PROCEDURE ' + @procedure + ' WITH ENCRYPTION AS ' + REPLICATE(cast('-' as nvarchar(max)), 40003 - @procNameLength)
EXECUTE (@fake_01)
SET @fake_encrypt_01 = (
SELECT
imageval
FROM
sys.sysobjvalues
WHERE
objid = object_id(@procedure)
and valclass = 1
and subobjid = 1
)
SET @fake_01 = 'CREATE PROCEDURE ' + @procedure + ' WITH ENCRYPTION AS ' + REPLICATE(cast('-' as nvarchar(max)), 40003 - @procNameLength)
SET @intProcSpace = 1
SET @real_decrypt_01 = replicate(cast(N'A' as nvarchar(max)), (datalength(@real_01) / 2))
SET @intProcSpace = 1
WHILE @intProcSpace <= (datalength(@real_01) / 2)
BEGIN
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1, NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^ (UNICODE(substring(@fake_01, @intProcSpace, 1)) ^ UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))
SET @intProcSpace = @intProcSpace + 1
END
insert #output (real_decrypt) select @real_decrypt_01
declare @dbname sysname
declare @BlankSpaceAdded int
declare @BasePos int
declare @CurrentPos int
declare @TextLength int
declare @LineId int
declare @AddOnLen int
declare @LFCR int
declare @DefinedLength int
declare @SyscomText nvarchar(max)
declare @Line nvarchar(255)
Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0
CREATE TABLE #CommentText ( LineId int ,Text nvarchar(255) collate database_default )
DECLARE ms_crs_syscom CURSOR LOCAL FOR
SELECT real_decrypt from #output
ORDER BY ident
FOR READ ONLY
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT FROM ms_crs_syscom into @SyscomText
WHILE @@fetch_status >= 0
BEGIN
SELECT
@BasePos = 1
SELECT
@CurrentPos = 1
SELECT
@TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
SELECT
@CurrentPos = CHARINDEX(char(13) + char(10), @SyscomText, @BasePos)
IF @CurrentPos != 0
BEGIN
While (isnull(LEN(@Line), 0) + @BlankSpaceAdded + @CurrentPos - @BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT
@AddOnLen = @DefinedLength - (isnull(LEN(@Line), 0) + @BlankSpaceAdded)
INSERT
#CommentText
VALUES
(
@LineId
,isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')
)
SELECT
@Line = NULL
,@LineId = @LineId + 1
,@BasePos = @BasePos + @AddOnLen
,@BlankSpaceAdded = 0
END
SELECT
@Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos - @BasePos + @LFCR), N'')
SELECT
@BasePos = @CurrentPos + 2
INSERT
#CommentText
VALUES
(@LineId, @Line)
SELECT
@LineId = @LineId + 1
SELECT
@Line = NULL
END
ELSE
BEGIN
IF @BasePos <= @TextLength BEGIN
While (isnull(LEN(@Line), 0) + @BlankSpaceAdded + @TextLength - @BasePos + 1) > @DefinedLength
BEGIN
SELECT
@AddOnLen = @DefinedLength - (isnull(LEN(@Line), 0) + @BlankSpaceAdded)
INSERT
#CommentText
VALUES
(
@LineId
,isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')
)
SELECT
@Line = NULL
,@LineId = @LineId + 1
,@BasePos = @BasePos + @AddOnLen
,@BlankSpaceAdded = 0
END
SELECT
@Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength - @BasePos + 1), N'')
if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength + 1) > 0
BEGIN
SELECT
@Line = @Line + ' '
,@BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT FROM ms_crs_syscom into @SyscomText
END
IF @Line is NOT NULL
INSERT
#CommentText
VALUES
(@LineId, @Line)
select
Text
from
#CommentText
order by
LineId
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText
ROLLBACK TRAN
DROP TABLE #output
GO
