Are there any things to consider before granting VIEW SERVER STATE and VIEW DEFINITION to Developers?

Question

I would like to grant below to permissions to Developers group, on Production SQL Server:

VIEW SERVER STATE
VIEW DEFINITION (server level)

This is done to make them able to query some of dynamic management views and functions, view performance data, as well as see code (definitions) of all stored procedures and functions

Are there any drawbacks or any reasons why this is not good in certain scenarios ?
Any possible unwanted side effects of granting above permissions ?

Second question - if I grant VIEW DEFINITION in master database, that makes it server-level and it doesn't have to be granted in any user database ? Same with SHOWPLAN ?

Answer 1

For VIEW DEFINITION / VIEW ANY DEFINITION you are probably fine given that the developers likely already have access to the source-code.

For VIEW SERVER STATE, that does control access to a wide range of functionality, so you need to be more deliberate and cautious when granting this permission. The primary consideration is: How open-ended vs constrained does the access need to be? The issue is that there is no granularity to VIEW SERVER STATE: it's either all or none.

Do the developers need full ad hoc query access, or do they simply need the ability to execute some number of pre-defined queries that you will provide? If they need full ad hoc query access then you will likely need to grant them VIEW SERVER STATE. But, if you can get away with providing a limited number of queries, then you don't need to grant them anything (outside of EXECUTE permission on the stored procedures that contain the queries that are providing). In this case you would grant the stored procedures the VIEW SERVER STATE permission (and/or ALTER TRACE permission). The developers would only be able to make use of those elevated permissions via the stored procedures. And, if any developer tries to get sneaky and update one of the stored procedures to make use of the elevated permission(s) in another way, then whatever stored procedures was updated would automatically lose the elevated permissions.

This is all possible via Module Signing. For an example, please see my answer to Execute Permissions for a Store Procedure that creates databases (also here on DBA.StackExchange).

Once you have this security mechanism in place (i.e. the certificate and the certificate-based login and/or user, depending on the scope of permissions you are needing to grant), it can be applied to any number of stored procedures, triggers, TVFs (except for inline), and UDFs. If a second permission is needed, then if the permission is always needed along with the initial permission, just add it to the same certificate-based principal (login and/or user). If the second permission is only needed in some cases, then create another certificate, and another certificate-based principal from the 2nd certificate, assign the other permission to the 2nd certificate-based principal, and execute ADD SIGNATURE again, using the 2nd certificate, on the objects needing the other permission (you can add multiple signatures to objects, hence combining permissions).

Answer 2

VIEW DEFINITION is a database permission. There is a corresponding server permission VIEW ANY DEFINITION you can add. If you want grant some logins access to view server state and any all object metadata, you can do it like this:

create server role developers
grant view any definition to developers
grant connect any database to developers 
grant view server state to developers

You can grant SHOWPLAN for each database, or grant ALTER TRACE at the server level. ALTER TRACE also allows the user to create SQL Traces which you might not want to allow developers to do on a production server. This is all documented under Permissions.

Answer 3

Few notes from Dan Guzman's link:

VIEW SERVER STATE

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 =)

From a security perspective, you run the risk of letting a user see details about your weak spots, Example, a malicious user could view your most common wait stats, 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.

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