locked
access to view syscacheobjects permisson denided RRS feed

  • Question

  • I am planning to grant select permission on this view  in master database. (master.sys.syscacheobjects)

    grant select on [master].[dbo].[syscacheobjects] to [user] ;

    GRANT SELECT ON OBJECT::[sys].[fn_get_sql]  TO [user]  AS [dbo];

    GRANT SELECT ON OBJECT::[sys].[syscacheobjects]  TO [user]  AS [dbo];

    I granted  the permission and still no luck

    SELECT

    *   FROM[master].[sys].[syscacheobjects]

    Msg 297, Level 16, State 1, Line 1

    The user does not have permission to perform this action.

    any idea would be greatly appreciated

    Thanks

    Wednesday, November 7, 2018 6:38 AM

Answers

  • You need to grant the user the VIEW SERVER STATE permission to view syscacheobjects e.g.

    GRANT VIEW SERVER STATE TO [user]

    Wednesday, November 7, 2018 6:50 PM
  • Hi ashwan,

    This system table sys.syscacheobjects is included as a view for backward compatibility. This feature will be removed in a future version of Microsoft SQL Server. I recommend that you use the current SQL Server system views instead(e.g. , sys.dm_exec_cached_plans, sys.dm_exec_sql_text, sys.dm_exec_plan_attributes and sys.dm_exec_cached_plan_dependent_objects). Please refer to the documents from sys.syscacheobjects and Mapping System Tables to System Views.

    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 8, 2018 1:24 AM
  • Hi,

    You also need to grant execute permission on the master database where those stored procedures actually reside.

    use master
    go
    grant EXECUATE on sp_OACreate to user
    GO

    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Thursday, November 8, 2018 5:11 AM

All replies

  • You need to grant the user the VIEW SERVER STATE permission to view syscacheobjects e.g.

    GRANT VIEW SERVER STATE TO [user]

    Wednesday, November 7, 2018 6:50 PM
  • Hi ashwan,

    This system table sys.syscacheobjects is included as a view for backward compatibility. This feature will be removed in a future version of Microsoft SQL Server. I recommend that you use the current SQL Server system views instead(e.g. , sys.dm_exec_cached_plans, sys.dm_exec_sql_text, sys.dm_exec_plan_attributes and sys.dm_exec_cached_plan_dependent_objects). Please refer to the documents from sys.syscacheobjects and Mapping System Tables to System Views.

    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 8, 2018 1:24 AM
  • Hi Kevin

    Its work and then getting different error

    The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

    Thursday, November 8, 2018 3:54 AM
  • Hi Puzzle I am executing  old Stored SP . Recreating old SP is not the plan
    Thursday, November 8, 2018 3:55 AM
  • Hi,

    You also need to grant execute permission on the master database where those stored procedures actually reside.

    use master
    go
    grant EXECUATE on sp_OACreate to user
    GO

    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Thursday, November 8, 2018 5:11 AM