How do I view the definition for a stored procedure that cannot be modified in SQL Server 2008 R2?
sql-server, sql-server-2008-r2, stored-procedures
Solution
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/
Problem
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) ```