Trying to avoid granting Sysadmin to SSIS Proxy account

  • Simon-220850

    SSCrazy

    Points: 2789

    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

     

  • Eric M Russell

    SSC Guru

    Points: 125026

    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

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    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

    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

    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

     

     

     

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

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