When registering a SQL Server instance to be monitored by Ignite, it will set up a login with SYSADMIN privileges or use an existing SYSADMIN login.  Ignite requires a user with SYSADMIN privileges for the initial registration and to perform 'Show Plans' for the SQL statements it captures.  When the Ignite monitor first starts, it will also turn on SQL Server trace flag 2861 in order to get text for quick running sql statements. (i.e. DBCC TRACEON (2861, -1)).

To Remove SYSADMIN Privileges From Monitor Login

It is possible after the Ignite monitor has been started for the first time, (and it has captured some initial data), to remove the SYSADMIN privilege from the Ignite user.  However, you will need to give specific privileges to the underlying objects Ignite needs to access.  If SYSADMIN is removed, the 'Show Plans' and the 'Real Time - Kill Sessions'  features of Ignite will no longer work because the Ignite user requires a high level of privileges to perform those actions.

The following process can be used to remove the SYSADMIN privilege:

1. Stop the Ignite monitor for the SQL Server instance.
2. Remove the SYSADMIN privilege from the Ignite user.
3. Run the following commands to grant specific privileges. Replace the user CONFIO\confio with your own username:

CREATE LOGIN [CONFIO\confio] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

USE [master]
EXEC sp_MSforeachdb '
  USE ?
  CREATE USER [CONFIO\confio] FOR LOGIN [CONFIO\confio] WITH DEFAULT_SCHEMA=db_datareader
'
GO

GRANT VIEW ANY DATABASE TO [CONFIO\confio]
GRANT VIEW SERVER STATE TO [CONFIO\confio]
GRANT VIEW ANY DEFINITION TO [CONFIO\confio]
GO

USE [master]
GRANT SELECT ON sys.dm_exec_query_stats TO [CONFIO\confio]
GRANT SELECT ON sys.fn_get_sql TO [CONFIO\confio]
GRANT EXECUTE ON sys.sp_OADestroy TO [CONFIO\confio]
GRANT EXECUTE ON sys.sp_OAGetErrorInfo TO [CONFIO\confio]
GRANT EXECUTE ON sys.sp_OACreate TO [CONFIO\confio]
GRANT EXECUTE ON sys.sp_OAGetProperty TO [CONFIO\confio]
GRANT EXECUTE ON sys.sp_OAMethod TO [CONFIO\confio]
GRANT EXECUTE ON sys.sp_OAStop TO [CONFIO\confio]
GRANT EXECUTE ON sys.sp_OASetProperty TO [CONFIO\confio]
GRANT CREATE TABLE TO [CONFIO\confio]
GO

Note: If the SYSADMIN privilege is taken away from the Ignite user, it is recommended to turn off the Historical Plan Collection feature of Ignite because it will receive errors.  To turn this feature off, navigate to the Options > Advanced Options screen and select the SQL instance from the dropdown at the bottom right.  Change the PLAN_COLLECTION_ENABLED option to False.