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?
What DBCC commands are used?
The required permissions for database monitoring have been reduced to the minimum. SYSADMIN is not longer required 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.
This role is used by numerous collections including those for Log Shipping, Job monitoring, Database information, Log shipping, and statistics.
The role db_ddladmin is needed to collect the following metrics:
Beginning in SQL Server 2012 (11.x) SP1 and higher sys.dm_db_stats_properties dmv became available and can be used instead of dbcc show_statistics, but from permission perspective there is no change since sys.dm_db_stats_properties still need at least dd_ddladmin permission: "requires that the user has select permissions on statistics columns or the user owns the table or the user is a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role."
Enhancement ID SSFOG-999 has been looged to review the permissions required to use something with less permissions than db_ddladmin.
The following DBCC commands are used