SQL Server – Proper Permissions for ETL User

sql serverssis

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

This is our ETL in a nutshell:

  1. Load 100+ tables from 5 different SQL Servers into stage in DW
  2. Call procedures on stage and ODS databases to transform the data
  3. 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

Best Answer

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.

Related Question