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.
GRANT VIEW Definition TO PUBLIC
Không có nhận xét nào:
Đăng nhận xét