SQL Server Permissions Required for Reporting

The following user permissions are required for the Windows user on the following servers and SQL components. 

For all deployments, whether all servers are installed on a single instance or if they are split across multiple servers, the Windows user must be a domain user and the SQL Reporting solution cannot use an SQL account.

Server*Permission TypePermission or RoleAssociated ObjectNeeded ForNotes
SQL Reporting ServerOS permissionLocal AdministratorWindows userSetup
OS permissionInteractive logonWindows userSetup/Operation
MS SQL Server

OS permissionAbility to login to the SQL ServerWindows userSetup/Operation
Database role

db_owner role

Datamart and Staging databasesSetup/OperationTo allow Prognosis data to be stored and processed
SQL permission

View Server state

SQL ServerSetup/Operation
SSIS Server Component

OS permissionAbility to log in to SQL serverWindows userSetup/Operation
Database roleSQLAgentUserRole[msdb] databaseSetup/Operation
Server rolesysadmin roleSSIS ServerSetupThe sysadmin server role is required, as the SetupInsightSSIS.sql script will create an SQL Agent Proxy for the SSIS subsystem, which can only be done by a sysadmin.
SSRS Server Component

OS permissionAbility to log in to SQL serverWindows userSetup/Operation
SSRS roleContent Manager roleSSRS root directorySetup
Database roledb_datareader roleDatamart databaseOperationTo allow the report user to run reports based on the datamart data.
SQL permissionGRANT/EXECUTEVarious - depends on report packOperationTo allow the report user to execute stored procedures used during report generation.

* Server - may be the same server in some deployments, and for the SQL Component may also be the same server or split across multiple servers.

Provide feedback on this article