Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I view the definition for a stored procedure that cannot be modified in SQL Server 2008 R2?

I am trying to view a stored procedure that resides in a database running in SQL Server 2008 R2. I have found the stored procedure under [dbName] > Programmability > Stored Procedures > dbo.[sprocName] but it has a little padlock icon next to it and when I right click on it to modify it, the modify option is grayed out.

I then tried to access the definition by querying object in the database like this:

use [dbName];
select * from sys.sql_modules
where definition LIKE '%[sprocName]%'

This does not yield the definition for the desired stored procedure, but the query does work for stored procedures that I can already modify.

How do I view the definition of this stored procedure without gaining modify privileges?

Update

It appears that the Sproc is in fact encrypted based on this error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Script failed for StoredProcedure 'dbo.[sprocName]'.  (Microsoft.SqlServer.Smo)

------------------------------
ADDITIONAL INFORMATION:

Property TextHeader is not available for StoredProcedure '[dbo].[sprocName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  
    The text is encrypted. (Microsoft.SqlServer.Smo)
like image 772
quakkels Avatar asked Jul 02 '13 17:07

quakkels


1 Answers

In order to do this you will need to have all the necessary permissions (SA basically) but here is a site that you should be able to follow to fix the problem.

http://sqljunkieshare.com/2012/03/07/decrypting-encrypted-stored-procedures-views-functions-in-sql-server-20052008-r2/

like image 86
Wes Palmer Avatar answered Nov 09 '22 16:11

Wes Palmer