All we need is an easy explanation of the problem, so here it is.
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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
VIEW DEFINITION /
VIEW ANY DEFINITION you are probably fine given that the developers likely already have access to the source-code.
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).
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.
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂