如何查看无法在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.

我试图查看驻留在SQL Server 2008 R2中运行的数据库中的存储过程。我在[dbName]>可编程性>存储过程> dbo。[sprocName]下找到了存储过程，但它旁边有一个小挂锁图标，当我右键单击它来修改它时，修改选项显示为灰色。

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:

看来Sproc实际上是根据以下错误消息加密的：

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)

1 个解决方案

#1

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.

为了做到这一点，你需要拥有所有必要的权限（基本上是SA），但这里是一个你应该能够遵循的网站来解决问题。

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

#1