20

I just installed SQL server 2014 however, I have a problem creating the database or even view properties.

I get "VIEW SERVER STATE permission was denied on the object 'server', database 'master'. Microsoft Server, Error:300"

I cannot alter my server roles, I have only "Public" role for some reason. any ideas?

Mindan
  • 979
  • 6
  • 17
  • 37
  • Did you add your account to sysadmin role while installing? – sepupic May 22 '17 at 06:45
  • I cannot recall that I did so, is there any way that I can add my account now? – Mindan May 22 '17 at 06:53
  • Yes you can if you are local windows admin. You should start the server in singl user mode using -m and add yourself as sysadmin. Here is a step-by-step link https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out – sepupic May 22 '17 at 06:55

3 Answers3

26

connect to ssms using administrator(sysadmin) account and execute the below 

USE MASTER
GO
GRANT VIEW SERVER STATE TO [username]
Alexan
  • 8,165
  • 14
  • 74
  • 101
Ramkumar Sambandam
  • 487
  • 1
  • 6
  • 11
  • After executing the code above, if the previously denied query returned **no data**, then the user running the query need to be granted the Server Role: `sysadmin` – Abdul Rahman Kayali Nov 15 '19 at 12:06
  • 1
    You can also grant this by navigating in the SSMS to Security - Logins, right click on the account and display Properties - Securables. On this list you can check 'Grant' on the View Server State option. – mggSoft Oct 28 '21 at 10:10
3

From SQL Server Expert

Login user do not have VIEW SERVER STATE permission and granting the required permission solves the issue.

USE MASTER
GO
GRANT VIEW SERVER STATE TO <username>
Mukesh Modhvadiya
  • 2,178
  • 2
  • 27
  • 32
3

Just for reference, this problem seems to be related to this bug.

I have the same problem with SQL Server Management Studio 2012.

If anyone finds stuck with this problem, try to update the SQL Server Management Studio. This way, you don't need to grant the permission VIEW SERVER STATE to the user.

Using SQL Server Management Studio 2014 (12.0.4213.0) now, and the problem seems to be gone

Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
EeNiArT
  • 70
  • 5
  • Mr. Rauch! why u remove the reference link – EeNiArT Oct 07 '17 at 10:55
  • 2
    This answer was copied from https://dba.stackexchange.com/a/141433/5203 – GSerg Oct 19 '17 at 19:16
  • Yes it is copied , i have also added the reference link in it but some one removed it – EeNiArT Oct 20 '17 at 07:46
  • 2
    Nobody removed the reference link. It was formatted into the text. Copying an answer without attribution, like you did, is called [plagiarism](https://stackoverflow.com/help/referencing). – GSerg Oct 20 '17 at 08:45