Trying to avoid granting Sysadmin to SSIS Proxy account

Question

Trying to avoid granting Sysadmin to SSIS Proxy account

Simon-220850

SSCrazy

Points: 2789
More actions
April 10, 2019 at 3:22 pm

#3093008

We have an ETL process involves a couple of packages
- one that receives a Database backup from and external organisation and restores it to a staging server (this database obviously doesn't contain any users mapped to logins or accounts in our organisation)
- and a second package that queries that tables on that restored external database and loads it into an operational data store
The packages run on a dedicated ETL Server and run using a Proxy account with access to the SSIS subsystem. Since there are no users on the restored database that are mapped to a login on our ETL server, the only way that we can see to get the external database backup restored and the data subsequently loaded table by table into our ODS is for proxy account to have Sysadmin privileges. Although it pains me to grant sysadmin privileges, I can see no other to read data from a database with no mapped users or to create a user in this database. Is there a way around this or am I asking the impossible

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125562 More actions · Answer 1

Maybe add the proxy account to one of the other server roles.

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-2017

For example:

"dbcreator: Members of the dbcreator fixed server role can create, alter, drop, and restore any database."

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

TangoVictor SSChampion Points: 10975 More actions · Answer 2

Based on “dbcreator: Members of the dbcreator fixed server role can create, alter, drop, and restore any database.” does that make the proxy account the owner of the database then? If so then that solves the problem.

In the initial post it says the database is restored but there's no details on that. Is this being done using the proxy with admin rights? If not, does that account become the owner?

Simon-220850 SSCrazy Points: 2789 More actions · Answer 3

Thanks Eric - that Server Roles Venn-Diagram on the MS Docs link is really useful - had been searching for something like that

The SSISProxy has been granted the server dbcreator role, I've come to the conclusion that what I am trying to accomplish with the SSISProxy account can only be achieved granting it sysadmin server role as subsequent packages run using the proxy then need to be able to access the tables in the restored database.

Simon-220850 SSCrazy Points: 2789 More actions · Answer 4

Hi AlphaTangoWhiskey

The Database ownership is actually really interesting question. Since the SSISProxy Account is creating the database, it does indeed become the owner of the database

both the script

SELECT suser_sname( owner_sid ), * FROM sys.databases

...and Database General Tab shows the SSISProxy login as the owner

however under the Files Tab - under Owner - it tries to display related user which is obviously empty - since the database comes from outside the organisation

...so although the Login SSISProxy is technically the DB owner, because it doesn't have a mapped login in the database it can't access the tables. I think I am going to have to grant the Proxy Account Server Sysadmin privileges for it to accomplish both the task of accessing the tables

Thanks