Security and Performance implications of "View Server State"

Question

This question points out that "View Server State" permission is required for various DMV's (dynamic management views), but I can't find anything about who you do and do not want to grant the permission to.

Now of course I understand "least permissions", and why you wouldn't want to just grant it to anybody, but I can't find any guideance on how to evaluate whether it SHOULD be granted or not.

So, my question: What are the security and performance implications of granting a user "View Server State" permission. What can they do that they maybe shouldn't be allowed to do...

Update: one implication is that the user will be able to use DMV's to look at queries. If the queries or query parameters can contain confidential information that the user wouldn't otherwise be able to see, allowing VIEW SERVER STATE would allow them to do so (i.e. dob = or ssn =).

Update 2: Microsoft has recognized the need for more granular permissions - https://techcommunity.microsoft.com/blog/sqlserver/new-granular-permissions-for-sql-server-2022-and-azure-sql-to-improve-adherence-/3607507, which at least mentions that some of the views are more sensitive than others.

Answer 1

There are no significant performance issues that I can think of from granting this permission. From a security perspective, you run the risk of letting a user see what you most details about your weak spots, so for example, a malicious user could view your most common wait stats are, which could help them target a DoS attack against your server.

Is this possible? Definitely. Is this likely? I'm compelled to say No, but remember that it is estimated that 90 percent of attacks against companies are from internal attackers.

Answer 2

As an administrator you would view this information as being in your domain (performance / index usage / etc) but there are potentially compelling reasons that a development organization would want this information for a large legacy system they support- identifying zombie tables that are only touched by maintenance processes for example.

In the end it always ends up being an issue of "luck and generosity" since the call on whether any particular request is justified ends up being a soft choice and not a crisp formula. The use of best practice patterns without looking at context is itself a pretty nasty anti-pattern and reality is that many approach their positions with "talk to the hand" as a starting point.

Answer 3

Regarding performance implications, I am not aware of any for this or any other permission.

Regarding:

What can they do that they maybe shouldn't be allowed to do

Simply put, they can see things that maybe they shouldn't be seeing. And don't think of this in terms of just SQL Server. This particular permission also governs DMVs such as sys.dm_os_sys_info and quite a few others that provide insight into the host machine (hardware, services, etc). You don't always know what info can be used against you. And, even if you are ok with someone seeing everything allowed by this permission now, sometimes DMVs are added in Service Packs / Cumulative Updates, and so maybe a new piece of info gets exposed that you aren't aware of.

I can't find any guidance on how to evaluate whether it SHOULD be granted or not.

Since you already mentioned giving people the minimum permissions needed, what this really comes down to is: does someone need this permission for ad hoc usage? Meaning, does someone need the flexibility of coming up with their own queries? Would creating one or more stored procedures and/or multi-statement TVFs work? If so, then you don't need to grant permissions any user (who is then free to anything allowed by that permission), and instead you grant the permissions to the code (which only does what it is coded to do). Module Signing is how you accomplish this. The general concept is:

1. Create the stored procedure(s) and/or multi-statement TVF(s) to perform the desired action(s).
2. Grant EXECUTE on these modules to whatever user and/or roles need to perform these actions
3. Create a certificate
4. Sign the module(s) using that certificate (using ADD SIGNATURE)
5. Copy the certificate to the [master] database (i.e. create a certificate in [master] using the public key of the certificate used to sign the module(s).
6. Create a login from the certificate copied to [master]
7. Grant whatever instance-level permissions are necessary to that certificate-based login (which can include adding it to instance-level roles).

For some examples, please see:

• How to maintain database ownership restoring across domains?

• Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level

Answer 4

It's a security problem. You can never go wrong if you follow the Principle of Least Privileged. In other words, if an authenticating principal doesn't need a particular permission, then don't give it to them. Do you give out information regarding the type of locks on your door to other people that don't need to know that about your house? I would hope not. They probably wouldn't do anything, but it's still not prudent.

If we based data principles off of luck and generosity, we'd be in bigger trouble quite a bit more often. Security is an aspect where you should only grant when you can defend why you granted. You're simply giving somebody more information than they need to know. Don't do it. Server state is still sensitive.