none
Permission error message on sql server 2014 RRS feed

  • Question

  •  

    Hi All,

    I have a very interesting permission issue on sql server 2014, where a security group have a "read permission" on the database but they receive the following error message; but when they click away from the error message the issue is gone and they are able to have the "read access" they require.

    "VIEW SERVER STATE permission was denied on object 'server', database 'master'

    Would anyone let me know why this happens please?

    Thank you in advance!

    Wednesday, June 10, 2015 1:25 PM

All replies

  • Hello,

    VIEW SERVER STATE is a server level permission, not on database. Which tool is used to run the queries; as I guess the tools query some Server meta data, when the user query is executed?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 10, 2015 1:36 PM
    Moderator
  • For this Group, is the default database set to Master?  If so set the default database to a database they already have access to.

    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, June 10, 2015 1:38 PM
  • The query is run from management studio.

    Thank you

    Wednesday, June 10, 2015 2:29 PM
  • Then I guess its caused by either object explorer or intellisense; turn both off

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 10, 2015 3:22 PM
    Moderator
  • Exactly what are they doing when they get this error? It seems that SSMS is submitting some SQL whcih required VIEW SERVER STATE, potentially when it shouldn't do that. But since we don't know they steps you take to get the error we can't comment on whether it is a bug or by design...

    A wild guess is that there is some add-in installed on SSMS and this plug-in executes some SQL which requires this permission.


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, June 10, 2015 3:40 PM
    Moderator
  • thank you for your reply!

    To make it clear, I'm just giving a "read permission" to a security group and they get the following error message:

    "VIEW SERVER STATE permission was denied on object 'server', database 'master'", but the only good thing is that when they click away from the error message they are able to view the objects. When the user tries to view the database property they get the following message:

    "VIEW SERVER STATE permission was denied on object 'server', database 'master'" but cannot get any further to view the db properties.

    Thank you!

    Thursday, June 11, 2015 8:00 AM
  • I'm sorry, but you don't give us anything we can use to try to reproduce or understand what is happening. Exactly what buttons etc are pressed when they get that email? Imagine that I try to reproduce what you are seeing, what program would I use and what would I type/click?

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, June 11, 2015 9:07 AM
    Moderator
  • There is no email which the user is getting. It is just a standard ssms "read permission" issue where the user logs in to management studio and expands "Databases" and tried to view the database they have "read permission" to

    Thanks!

    Thursday, June 11, 2015 9:20 AM
  • Sorry, "email" should have been "error". OK, now we are getting somewhere. So they login to your SQL Server using SSMS, and when they expand the databases folder they get this error. Correct?

    Does the same thing happen if they try SSMS on some other computer? Did you install any plug-in to SSMS?


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, June 11, 2015 9:31 AM
    Moderator
  • Thank you for your reply!

    No, Thereare no other plug-ins installed.

    I have tried to get another person in the same security group to login which they also have the same "read permission" and the same error.

    Thank you

    Thursday, June 11, 2015 10:02 AM
  • And you have tried doing this from several installations of SQL Server Management Studio?

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, June 11, 2015 10:03 AM
    Moderator
  • This error pops up when you try to expand a node in the SSMS that requires some kind of system catalog access. The pop up can be dismissed and the user can proceed along. It can be resolved by giving the following server level permission -

    USE [master]

    GO

    GRANT VIEW SERVER STATE TO <YOUR LOGIN/GROUP NAME>


    - Aalamjeet Rangi | (Blog)

    Thursday, June 11, 2015 10:06 AM
  • Thanks for your reply!

    I want to know are there not a any security risks as the whole point of giving a user a "read permission is to give the  least permission as possible?

    Thursday, June 11, 2015 10:45 AM
  • This rings a bell. Can you please post the version of SSMS as reported in
    Help->About?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 11, 2015 10:49 AM
  • The version is 12.0.2000.8

    Thank you

    Thursday, June 11, 2015 10:52 AM
  • It sounds like you are running into this bug:

    https://connect.microsoft.com/SQLServer/feedback/details/791630/cannot-view-properties-of-database-when-using-db-owner-account

    Microsoft says in the comments We have corrected this for the next version of SQL 14. This was posted in July 2013, why I would expect the fix to be available in SQL 2014 RTM which is what you have. On the other hand, there is a user comment to the Connect item which reports the same issue with SQL 2014 RTM.

    I don't have a version of SSMS 2014 to test with right now, but you could apply Service Pack 1 for SQL 2014 to see if it helps. The issue is certainly in SSMS - it's running something which it should not do for a less-privileged user. (I seem to recall that I have looked in the issue in the past, but it was a while ago.)

    I'll try to find the time to look into this more tonight.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 11, 2015 12:14 PM
  • Thank you,

    I'll look forward to your response!

    You just saved me  a lot of time of research!

    Thursday, June 11, 2015 12:48 PM
  • Yes, the downside of giving VIEW SERVER STATE is that now use would be able to query the server scoped system catalogs and DMVs to look at server health information. You need to be sure if that is acceptable in your environment. If not acceptable, then ask the user to simply dismiss/ignore the error message.

    Dynamic Management Views and Functions (Transact-SQL)
    https://msdn.microsoft.com/en-us/library/ms188754.aspx


    - Aalamjeet Rangi | (Blog)

    Thursday, June 11, 2015 2:12 PM
  • Opening up security is not what I consider an acceptable solution to the problem. If this is indeed the case, then the bug in Management Studio need to be addressed and fixed. I definitely recommend following Erland's recommendations in this thread.

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, June 11, 2015 4:15 PM
    Moderator
  • As they say, the plot thickens...

    I tested the scenario (or what I believe to be the scenario), and I get the error message from SSMS 2012 SP2. I don't get it with SSMS 2014, neither with RTM nor SP1.

    This leads me to one of two conclusions:

    1) Your scenario is different, and the issue has not been fixed in this case.

    2) The bug is really in SMO and for reason reason your SSMS uses the wrong version of SMO. (Which I don't really know how do troubleshoot.)

    The bug as such is really trivial. It occurs in a very long batch, which includes this part:

    BEGIN TRY
         SELECT @cluster_name = cluster_name,
                        @quorum_type = quorum_type,
                        @quorum_state = quorum_state
         FROM sys.dm_hadr_cluster
    END TRY
    BEGIN CATCH
         IF(ERROR_NUMBER() != 297)
         BEGIN
                THROW
         END
    END CATCH

    Error 297 is The user does not have permission to perform this action, but you get error 300. The correct version has:

       IF(ERROR_NUMBER() NOT IN (297,300))

    To investigate, can you run Profiler when you run the operation? (Don't start Profiler until you are about to click Properties to reduce noise. If you are on a server with many users, filter for your login.) If you get the error message, this is probably the last batch in the trace. In my tests, the failing batch starts off:

         declare @HkeyLocal nvarchar(18)

    In total, this batch is over 200 lines of code.

    Could you post the full batch text here, so that we can see if it is the same batch, or maybe a different one (beside the error numbers, that is)?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 11, 2015 9:28 PM
  • USE master
    GO
    GRANT VIEW SERVER STATE TO [UserName]
    Wednesday, September 5, 2018 5:57 AM