Proper permissions for ETL user

I'm working on changing the permissions on our ETL to incorporate best practices.

This is our ETL in a nutshell:

• Load 100+ tables from 5 different SQL Servers into stage in DW

• Call procedures on stage and ODS databases to transform the data

• Log the executions to a logging database

This is scheduled via a job.

What would be the appropriate permissions for this ETLUser account? Here are my thoughts:

• Change to non-admin domain account

• assign db_datareader on source databases

• db_datareader, db_datawriter on Stage, ODS and Logging databases.

• db_executor role (custom) on Stage, ODS and Logging

• db_ddladmin on Stage (for truncating)

• Is a proxy account on the job required?

SQL Server 2012

Your thoughts are to the point.

The only thing I want to suggest is not to use the db_ddladmin account if you only want to truncate tables.
The minimum permission for truncating a table is ALTER, see Truncate Table. So you could make a custom database role with ALTER permissions on all tables you need to truncate. Assign that role to the ETLUser account.

A proxy account is in my opinion indeed the best way to run the package under the ETLUser account in an SQL Server agent job.

StackExchange Database Administrators Q#117202, answer score: 3