What are the user/permissions for the SQL Server cartridge and why have they changed from the previous release?
How are the permissions required by the agent comprehend with SOX limitations?
Why do the grant permission script for SQL Server includes db_datareader and db_ddladmin?
Are SYS ADMIN privileges are still required?
The required permissions for database monitoring have been reduced to the minimum. SYSADMIN is not longer required for SQL Server 2005/2008 and SOX limitations are met accordingly. There is a balance between what the agent can monitor on the one hand and security level on the other.
The level of permissions has been reduced for the cartridge from a sysadmin level to a specific level (almost read only - also caused by SOX limitations).
Most of the applications are working under read only mode.
The level of permissions has been kept as low as possible. The limitations came from part of the objects which needed a higher level of permissions, keeping the user monitoring the instances in a high level of security will make sure the security is kept as requested.
The grant permission script uses a delicate balance between security and the quality of monitoring required. Objects can be removed from the script, but this is not desired. If a lower level of permissions is used this can affect database monitoring.
Specific information on the required permissions can be found in the Foglight for Databases deployment guide available for download from here.
The role db_ddladmin is needed to collect the following metrics:
But for SQL Server 2012 SP1 and above, this is not required anymore.
An enhancement request has been created to get the permission script adjusted. ER number SSFOG-999.