How to Give Read Definition Permission to a User for all Procedures and Functions

Leave a comment (0) Go to comments

One of the developer complaint that he is getting a following error message while issuing sp_help, sp_helptext or using the object_definition() function to view the definition of object

EXEC sp_help Sales
Msg 15009, Level 16, State 1, Procedure sp_help, Line 66
The object ‘Sales’ does not exist in database ‘TEST’ or is invalid for this operation.

This error happens because user does not have permissions to the see the object metadata.  To resolve this issue, we need to explicitly give view definition permission to desired login.

The following SQL statement will built text, which you can select to execute to give permissions, to give view definition permission to a User for all Procedures and Functions.

SELECT 'grant VIEW DEFINITION on' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO [<<user_name>>]' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0    
and ( ROUTINE_TYPE='PROCEDURE'  or ROUTINE_TYPE='function'  )

where


  • INFORMATION_SCHEMA.ROUTINES gives all the information about procedures and functions on current database.
  • OBJECTPROPERTY function returns property of the given objectid and propertyname.
  • OBJECT_ID gives the object id of given object name.
  • IsMSShipped property determines whether object created during installation of SQL Server.

    By checking IsMSShipped property to 0 reveals that object are created by user and not system objects.

  • QUOTENAME returns a Unicode string with the delimiters added to make the input string a valid identifier which should used when contructing dynamic SQL as above from user input.

Or we can permit all users to have view definition permission by running the following code in a specific database

GRANT VIEW Definition TO PUBLIC
EOF - How to Give Read Definition Permission to a User for all Procedures and Functions, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.