SQL Server 2008 R2 error in generating the script

Question

I am trying to generated the script of a database on SQL Server 2008 R2

I got this error

Microsoft.SqlServer.Management.Smo.PropertyCannotBeRetrievedException: Property TextHeader is not available for StoredProcedure '[dbo].[p_SYS_GetQDNPassword]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

Could you help please?

I don't know what other information you need, but whatever you need, tell me please to give you.

Answer 1

Using the sp_helptext dbo.p_SYS_GetQDNPassword should give you the result:

"The text for object 'YourProcedureName' is encrypted."

This means that stored procedure is created using WITH ENCRYPTION, and SQL Server internally stores the text with the definition of the object in an obfuscated format

The actual definition of an object is stored in system table sys.sysobjvalues which is not directly accessible. By connecting to SQL Server using the Dedicated Administrator Connection (DAC) you can select the imageval column in which the information is stored

Click on Database Engine Query button:

The Connect To Database Engine dialog will pop out. Type the "admin:" prefix before the server name and you'll be connected to DAC

You can find useful info here in this article http://www.mssqltips.com/sqlservertip/2964/encrypting-and-decrypting-sql-server-stored-procedures-views-and-userdefined-functions/

When you're connected to DAC run the following script from the article:

   SET NOCOUNT ON
GO

ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS
BEGIN
 PRINT 'This text is going to be decrypted'
END 
GO

DECLARE @encrypted NVARCHAR(MAX)
SET @encrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = 'TestDecryption' )
DECLARE @encryptedLength INT
SET @encryptedLength = DATALENGTH(@encrypted) / 2

DECLARE @procedureHeader NVARCHAR(MAX)
SET @procedureHeader = N'ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength 
-   LEN(@procedureHeader)))
 EXEC sp_executesql @procedureHeader
 DECLARE @blankEncrypted NVARCHAR(MAX)
 SET @blankEncrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = 'TestDecryption' )

SET @procedureHeader = N'CREATE PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength 
-   LEN(@procedureHeader)))

DECLARE @cnt SMALLINT
DECLARE @decryptedChar NCHAR(1)
DECLARE @decryptedMessage NVARCHAR(MAX)
SET @decryptedMessage = ''
SET @cnt = 1
WHILE @cnt <> @encryptedLength
BEGIN
  SET @decryptedChar = 
      NCHAR(
        UNICODE(SUBSTRING(
           @encrypted, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @procedureHeader, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @blankEncrypted, @cnt, 1))
     )
  SET @decryptedMessage = @decryptedMessage + @decryptedChar
 SET @cnt = @cnt + 1
END
SELECT @decryptedMessage

Of course change the dbo.TestDecryption with your procedure name in the script :)

This script works for me, hope it helps

Answer 2

Use

sp_helptext dbo.p_SYS_GetQDNPassword 

and Try to alter the stored procedure and try to get script