When does the SQL Server Grant Privilege Script Add sysadmin and What to Do About It (4375155)

When does the SQL Server permission script used by Foglight grant the sysadmin role to a login?

Why does this happen, when is it needed, and what you can be done to avoid it. 

The script is designed to give a login all the permissions needed for Foglight to monitor SQL Server. These privileges are described in the Foglight for Databases deployment guide.

Below SQL Server 2005

On older versions of SQL Server (before 2005), the only way to do this was to make the login a sysadmin.

For SQL Server 2005 and newer

Even on newer versions, the script checks if the login already has the right permissions. If not, it may grant sysadmin, but only if absolutely necessary.

How does the script check the SQL Server version level?

The script uses this line to check the SQL Server version: 

IF (REPLACE(CONVERT(VARCHAR(2), SERVERPROPERTY('ProductVersion')), '.', '') < 9)

This means: if the version is less than 9 (SQL Server 2000 or older), it uses the old method that requires sysadmin.

Monitoring SQL Server 2005 or newer:

• The script does not automatically grant sysadmin.
• It first checks if the login already has the needed permissions.
• If the login has enough access, the script skips the sysadmin part and only grants specific permissions like:
  • VIEW SERVER STATE
  • VIEW ANY DEFINITION
  • EXECUTE on certain stored procedures
  • SELECT on monitoring tables

Workaround

To avoid sysadmin completely:

• Run the script using a sysadmin account, but target a non-privileged login.
• After the script runs, check the login’s permissions in SQL Server Management Studio (SSMS).
• If sysadmin was granted and you don’t want that, you can remove it manually in SSMS.

Optional: Use a modified script

If your organization has strict security rules, you can use a customized version of the script that never grants sysadmin, and only applies the minimum permissions needed. 

Customization Disclaimer 

The grant privileges script provided is known to work successfully in supported environments; however, it has not been officially tested by Quest Quality Control.

Any modifications to the script, intentional or unintentional, are considered customizations. As such, they fall outside the scope of support provided by Quest Support and Development.

Customers who require assistance with customized versions of the script should contact Quest Professional Services for guidance and implementation support.

As a best practice, always back up your SQL Server environment before executing any script that modifies permissions or database objects.

Status

Monitoring SQL Server 2000 instances is no longer supported by the Foglight for SQL Server cartridge.

As part of this change, Defect ID FOG-8384 was logged with R&D to remove legacy logic from the grant privileges script that conditionally assigns sysadmin privileges for SQL Server versions earlier than 9.0 (SQL Server 2005).

This ensures the script aligns with current support policies and avoids unnecessary elevation of privileges on modern SQL Server environments.