Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SSIS Package Credentials Expand / Collapse
Author
Message
Posted Friday, December 11, 2009 2:23 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 692, Visits: 397
I'm missing some things here (principal, job owner, ...). I doubt this (and the link to the summary on codeproject) is the recommended way if you want it to be "totally" secure (if that's ever possible).

The articles grant sysadmin and all MSDB roles. What for? This is way too much (in most cases). Plus, you are assigning all 3 msdb roles. You don't want this login to see ALL jobs, right? They should only see their own jobs. Check BOL and see that these roles follow a hierarchy (the most privileged one includes the permission from the other two). It's like people assigning a login the sysadmin server role AND the securityadmin role. The first one already includes all privileges...

You should be assigning a principal (needs only the public server role) access to the proxy, run the job step under the proxy, set the job owner to the SQL login, grant only the SQLAgentUserRole msdb role to the SQL login, set appropriate permissions on the dtsx file and folder it resides in, ...

We did it like this and it works fine. It needs a lot of work and maintenance though.




Post #832770
Posted Tuesday, July 20, 2010 4:44 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 10:53 PM
Points: 290, Visits: 714
Hi,

I have a doubt regarding the final article ('How to Schedule and Run a SSIS Package job') mentioned in this article.

Why do we need to give sysadmin server role to the 'executor login' as the whole concept of proxy account is for executing job for under priviledged logins.

Please could any one clarify?

Thanks in advance


John
Post #955421
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse