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:
- Create the stored procedure(s) and/or multi-statement TVF(s) to perform the desired action(s).
- Grant
EXECUTE
on these modules to whatever user and/or roles need to perform these actions
- Create a certificate
- Sign the module(s) using that certificate (using
ADD SIGNATURE
)
- 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).
- Create a login from the certificate copied to
[master]
- 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: