Thứ Hai, 12 tháng 12, 2011

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

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'  )
  • 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

 

Không có nhận xét nào:

Đăng nhận xét