Run an SSIS Package Under a Different Account

  • Comments posted to this topic are about the item Run an SSIS Package Under a Different Account

  • Food for thought, I can see many uses for this.

    Thanks

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • It was very useful! Thank you for the article.

    Vitor

  • "The ETLadmin account, which is a domain account that we use it to run SSIS packages, does not have the right permission to access views in that database even if it has sysadmin permission on that SQL Server instance."

    Could the author please explain this a bit further? How can a database user with sysadmin permissions on the instance not be able to read any data it wants? I admit that with only 4 years DBA experience I'm not a SQL Server guru, but I find it hard to believe that this is truely the case.

  • Short, sweet and to the point! My favorite type of article.

    I would also like an explanation as to how can sysadmin account not be able to read certain database tables?

  • It is true that a sysadmin loin has the permission to read all database objects. But many applications use views to control the data access.

    For example, user logins are saved in a table, and grouped by permissions or positions. A view displays different employee data according to the current login. If the login is a department supervisor, then the view only return employees from this department. If the login is CEO, then it returns all employees. Or only if the login is HR user, the review returns salary, otherwise salary is null.

    This permission control has nothing to do with the database permission.

    If you know the database structure, you can run a query against tables instead of views. But unfortunately sometime purchased third party applications are not allowing you to know the inside of the database. And in most cases, the queries can be very complicated.

    To make it worse, after an upgrade, tables could be changed.

  • Useful article on the use of Proxy accounts.

    Thanks for the article.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanks for all the comments. It's encouraging.:-)

  • Grey Cat (3/25/2010)


    Thanks for all the comments. It's encouraging.:-)

    You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • The facet thing works only on SQL 2008.

    If you were to run it in SQL2005, you'll have to use runas along with other parameters

  • Nice Article!!! Thanks for posting...

  • I have one question about this section:

    Enter the credential name - Enter the domain account, and password. Repeat the password in the 'Confirm password'. This account should have the right access to the application database.

    We use windows authentication to connect to the database. So if I put individual account, I can use network password. What if I need to add a group for credential, and the group doesn't have a network password. What should I put in password field?

  • I don't think a group account without password will work this way. The originally purpose of doing that is to bypass different accounts that do not having access...

  • One question for this:

    What if the credential's password get changed? I had been using one credential and created a proxy to run the SSIS package in SQL Agent jobs, but the job failed after the password for the account changed.

    any comments will be appreciated.

    cheers,

    Min

  • Please check the ProtectionLevel of the SSIS package.

    If it's not DontSaveSensitive, change it to.

    Hope that helps.

Viewing 15 posts - 1 through 15 (of 18 total)

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