Permissions to view Stored Procedure Property

hayward_ke


My Production servers are SQL Server 2005 x64. I would like to allow my developers the ability to look at permissions on production stored procedures but not be able to change those permissions or alter the production code. What has to be set to allow this sort of security.

Satya SKJ


To grant permissions on a stored procedure

1. In Object Explorer, connect to an instance of SQL Server 2005 Database Engine and then expand that instance.

2. Expand Databases, expand the database in which the stored procedure belongs, and then expand Programmability.

3. Expand Stored Procedures, right-click the procedure to grant permissions on, and then click Properties.

4. From Stored Procedure Properties, select the Permissions page.

5. To grant permissions to a user, database role, or application role, click Add.

6. In Select Users or Roles, click Object Types to add or clear the users and roles you want.

7. In the Explicit Permissions grid, select the permissions to grant to the specified user or role. For a description of the permissions, see Permissions.

   Selecting Grant indicates the grantee will be given the specified permission. Selecting Grant With indicates that the grantee will also be able to grant the specified permission to other principals.

hayward_ke

I have done that for the developer group and I still get this error on Stored Procedures

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

------------------------------

Property DefaultSchema is not available for Database '[HouseAccounting]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=DefaultSchema&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
The Developers can look at the properties of tables, views, and functions but not stoerd procedures.

Jens K. Suessmeyer

Are the stored procedures in another schema

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Madhu K Nair

If these developers are granted with DB_DDLAdmin role they will have more privillage than they required since u want these developers be able to only view the code. As far as i know there is no simple direct solution for this. What a workaround i can suggest is using "Execute As " feature of SQL 2005.

Create a storedprocedure which will get the body of sp from sysobjects. This sp will impersonate DBO, just try this

--

Steps 1 : Create a sp with Execute As DBO and grant execute permission to all the developers

Create proc spViewProcedureProperty

@ObjectName sysname --Pass the Storedprocedure name as input parameter.

WITH EXECUTE AS 'dbo'

as

SELECT OBJECT_DEFINITION (OBJECT_ID )

from SYS.OBJECTS WHERE TYPE = 'P' and Object_id=object_id(@ObjectName)

 

this will address one part of your problem, by doing this method developers can able to view the code.  But they can not alter.

For permission , what i feel is , u may need to make some wrapper like this

 

Madhu

 

Laurentiu Cristofor

If you just want to see information about the stored procedures, not the code, but the permissions on the procedures, have you looked into granting VIEW DEFINITION on the procedures

Thanks
Laurentiu

hazarder1980

Hello!

I have difficulties running:

Code Snippet

SELECT OBJECT_DEFINITION (OBJECT_ID(N'TableName')) AS [Object text]

It returns null. I've read that user who is running this query has to be granted VIEW DEFINITION. I set this grant (although I am owner) and it still returns null.

If i run:

Code Snippet

select object_id(N'TableName')

I get that TableName's ID so this isn't the issue.

Thanks for any kind of suggestion.