Friday, December 2, 2016

VIEW SERVER STATE permission was denied on object 'server', database 'master'. (Microsoft SQL Server, Error: 300)

Issue: I had to create few users for the first time and give access to execute views from user database. I have created logins for them and gated access to select from views and tables at object level but see below error when they select from views.

Error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

VIEW SERVER STATE permission was denied on object 'server', database 'master'. (Microsoft SQL Server, Error: 300)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.4100&EvtSrc=MSSQLServer&EvtID=300&LinkId=20476

------------------------------
BUTTONS:

OK

Fix: Granted view server state permission which fixed the issue.

Comments: SQL Server version is Microsoft SQL Server 2014 (SP1-GDR) (KB3194720) - 12.0.4232.0 (X64)
                Sep 23 2016 18:45:14
                Copyright (c) Microsoft Corporation
                Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

 Is it a known bug?

Query Used

USE MASTER
GO

GRANT VIEW SERVER STATE TO [LoginName]

2 comments:

  1. I'm confused on why you would need to provide this permission.

    Are they using the system DMV's or system functions?

    Did you change the user default database to something besides master? That is the default for new logins unless you change it.

    There is a chart in PDF format of all the available permissions and what they are for available for Sql 2016 at the following link...
    http://go.microsoft.com/fwlink/?LinkId=229142

    Unless they need those objects you are more secure revoking the View Server State permission, changing the default database to the one they will be working in and then Granting the Select permission on the views they need. Below I have included a simple test of this...


    -- 1. Create Login
    Create Login test_user
    WITH Password = 'xxxxxxxx',
    Default_Database = DBAsOnly,
    Check_Expiration = ON,
    Check_Policy = ON;
    Go


    -- 2. Create db user
    Use DBAsOnly;
    Go

    Create User test_user For Login test_user;
    Go


    /***************************************************/
    -- 3. Create user view, Unless already created.
    Create View dbo.vw_TestView
    As
    Select *
    From dbo.Testme
    Go

    Grant SELECT On vw_TestView To test_User As dbo;
    Go
    -- Note: If there are many views and multiple users
    -- it is usually easier to manage by creating
    -- database roles, adding the users to them
    -- and granting Select permissions to the role.

    /***************************************************/
    -- Test
    Execute As User = 'test_user';
    Go

    Select User_Name ()
    Go

    Select *
    From dbo.vw_TestView
    Go

    Revert;
    Go

    Select User_Name ()
    Go

    I hope this helps you and makes you a bit more secure.

    Have a great day.
    Richard
    r.l.dawson@usa.net

    ReplyDelete
  2. Thanks for the reply Richard. I did all above steps to create the users and granted read access to the views but I still see the error message. I was also wondering why we need to give this permission to select data from user views.

    ReplyDelete