3

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

Improve this question
1
  • 1
    The biggest security concern (but I know why you're doing it) is the db_ddladmin role membership. We've sometimes used a stored proc to get around that limitation by allowing the stored proc to EXECUTE AS a trusted user, and having it do the truncate.
    – Hannah Vernon ♦
    Commented Oct 6, 2015 at 20:47

1 Answer 1

Reset to default
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.