ETL User Permissions

After completing the installation of Analytics, set permissions for the SQL Server Integration Services (SSIS) and SQL Server Agent Services (SSAS) service accounts.

SSIS Service Account Permissions

  1. Open Windows Services Manager on the Data Warehouse machine. Identify the service account configured for SQL Server Integration Services from the Properties window as shown in the following image:

    service account configured for SQL Server Integration Services

  2. Open SQL Server Management Studio and connect to source database server (SIS and/or CRM).

  3. Navigate to Security >> Login.

  4. Right-click Login and click New Login to create a user with the service account identified in the step 1. Set dbo_owner permission to the source database (SIS/CRM) as shown below. If the login already exists, make sure that a minimum of db_owner permission is set on all source databases (SIS and/or CRM).

    new login with the new login with service account owner permission

  5. Similarly, follow the steps 2, 3 and 4 for the Data Warehouse database on the Data Warehouse SQL Server instance.

  6. Navigate to the SSISDB database and set the SQL Server Integration Services Account, SQL Server Agent Services Service Account to the ssis_admin role.

    ssis_admin role

SSAS Service Account Permissions

  1. Ensure that the SQL Server Agent Services logon account on the data warehouse server has sufficient privileges to log on to and process the semantic model database on the Analysis Services instance. If the SQL Server Agent Services logon account and SQL Server Analysis Services logon account are not the same, add the SQL Server Agent Services logon account to the Server Administrator role in the Analysis Server Properties, Security page:

    1. Open SQL Server Management Studio and connect to the SQL Server Analysis Services instance. Closed

      Analytics Server Properties

    2. Right-click the server name and select Properties to open the Analysis Server Properties window.

    3. Navigate to the Security page and click Add to enter the SQL Server Agent Services logon account.

    4. Click OK.

  2. Open the SQL Server Management Studio and connect to source database server (SIS and/or CRM).

  3. Navigate to Security >> Login.

  4. Right-click Login and click New Login to create a user with the SQL Server Agent Service account. Set dbo_owner permission to the source database (SIS/CRM). If the login already exists, make sure that a minimum of db_owner permission is set on all source databases (SIS and/or CRM).