I am trying to view a stored procedure that is in a database running in SQL Server 2008 R2. I found the stored procedure in [dbName] > Programmability > Stored Procedures > dbo.[sprocName] , but there is a small lock icon next to it, and when I right-click it to change it, the change option will not be available.
Then I tried to access the definition by querying the object in the database as follows:
use [dbName]; select * from sys.sql_modules where definition LIKE '%[sprocName]%'
This does not provide a definition for the desired stored procedure, but the query does work for stored procedures that I can already modify.
How to view the definition of this stored procedure without gaining change rights?
Update
It seems that Sproc is actually 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)
source share