Service account permissions on database objects

Products

• Information Centric Analytics

Issue/Introduction

The Information Centric Analytics (ICA) service account requires permissions on a handful of database objects, per the Permission Settings for the ICA Service Account Outside of Using the SQL Server sysadmin Role section of the ICA Administrator Guide. Your database administrator (DBA) may require clarification or justification for the following permissions:

• ALTER ANY LOGIN
• ALTER ANY LINKED SERVER
• SELECT on sys.dm_database_encryption_keys
• UPDATE on msdb.dbo.sysjobhistory

Environment

Version : 6.x

Resolution

The ICA Administrator Guide will be updated to provide the following details regarding these permissions:

• ALTER ANY LOGIN
  Deprecated. Referred to in a commented-out section of the installer's grant_database_permissions script but not called in the script or by any other sections of the installer and application code.
• ALTER ANY LINKED SERVER
  Necessary for ICA to create and delete linked servers when data sources are added through the Integration Wizard (stored procedure dbo.spIW_CreateRealLinkedServer).
• SELECT on sys.dm_database_encryption_keys
  Only required by an optional procedure to encrypt the ICA database:
  Encrypting the Information Centric Analytics Database
• UPDATE on msdb.dbo.sysjobhistory
  Used by ICA to update the job history message and status where staging procedures are run in parallel. This ensures the job history reflects all staging procedures rather than only the status (and corresponding message) of whatever the last procedure to complete happened to be. With this disabled, the SQL Server Agent's job history would be incomplete after these parallel operations.