SSMS does not have access to sys schema for master database - error is mssqlresourceserver schema sys execute permission denied

J Longstreet 41 Reputation points
2020-12-23T19:04:10.077+00:00

After trying to get a user to be able to execute a view - providing SCHEMA permissions, providing extended stored procedure permissions (directly) to the extended stored procedure specified to the error - no satisfaction. Oddly though, if the user does not right click the object so that it opens using the SSMS interface, but instead, writes the query ad hoc, there is no error.
Lately there has been some fairly tight restrictions come down the pipe about restricting extended stored procedure permissions and there is a notation from Microsoft the xp_ series will be deprecated in future versions.
I'm using the latest SSMS 18.7.1 and running on SQL Server 2019 Standard.
I have to wonder if this is a bug in the SSMS. Has anyone else run into this issue?

To recreate the issue, remove EXECUTE from xp_instance_regread

REVOKE EXECUTE ON [sys].[xp_instance_regread] TO [Public]

I even went so far as to create a role in each database (including master) and provided access to that extended stored procedure (to replace the permission removed from [Public] and added the users having the issue to those roles. Nothing seems to work (short of granting execute to public).

Has anyone encountered this strange situation? Did you find a work-around other than telling the user to run the query ad hoc rather than right clicking the object? (Select top 1000 xxx,yyy from Table)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
0 comments
Accepted answer
  1. Erland Sommarskog 101.8K Reputation points MVP
    2020-12-23T22:21:17.983+00:00

    I revoked permissions for public as you said. Then I connected to Object Explorer with an SQL login that owns a database, and in that database I tried to do SELECT TOP 1000 Rows, and indeed I got those errors.

    Next, I did this in master: 

    CREATE ROLE regreaders
CREATE USER MyUser
ALTER ROLE regreaders ADD MEMBER MyUser
GRANT EXECUTE ON [sys].[xp_instance_regread] TO regreaders

    Then I reconnected in Object Explorer (so that the tokens are picked up correctly.) I was now successful in running the SELECT TOP 1000 Rows.

    However, I would suggest that if you have been told to revoke permissions on xps for public, you should start to grant permissions to users by other means, but tell people that they will need to use SELECT statements. Else you are not obeying to the guidelines that your security people have given. Also, personally, I don't like adding users to the master database.

    As for editing posts, there is a cogwheel in the upper-right corner of your post. There is a menu which includes an Edit alternative (if the post is yours). For Comments, you find Edit under "More" below the post.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2020-12-24T05:45:05.09+00:00

    Hi @J Longstreet ,

    > I'm using the latest SSMS 18.7.1

    The SSMS 18.8 is the latest version, you can get it from here.

    > where would we go now to submit bugs - the original bug site for SQL Server is long gone.

    You can submit your issue to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server . This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with.

    Best regards,
    Cathy

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments