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

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) 
+4
source share
1 answer

To do this, you will need to have all the necessary permissions (SA mainly), but here is a site that you must complete to fix this problem.

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

+1
source

Source: https://habr.com/ru/post/1489333/


All Articles