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