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?
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)
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/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With