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:
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
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.
Post a Comment