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

Roles / Permissions for User Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 9:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:22 PM
Points: 55, Visits: 165
SQL2008

I have users that will need to
1) run jobs
2) modify and execute SSIS packages
Is there a built-in roles / permissions for that?

I have considered deploying SSIS packages to file system level and allowing users access to that folder (to enable them to modify and execute SSIS packages). Is there any downside / security concerns with this route?
Post #1489303
Posted Wednesday, August 28, 2013 1:49 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
If you allow access to the file system, realize that the SSIS packages will execute from wherever the dtexec command is given. So if it's given from the user's workstation, that's where it runs. When you use SQL Server Agent jobs, it's running from wherever the SQL Server Agent is running. That's the biggest hump to get over is to understand where the execution is.

For SSIS related roles in msdb, here's an explanation of what each does (from Books Online):

Integration Services Roles (SSIS Service)




K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1489402
Posted Wednesday, August 28, 2013 7:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:22 PM
Points: 55, Visits: 165
If you allow access to the file system, realize that the SSIS packages will execute from wherever the dtexec command is given. So if it's given from the user's workstation, that's where it runs.


Do you mean if the SSIS package is deployed to a server directory. And the user connects using a UNC path \\servername\foldername; when the user executes the package, is it still running from the user's workstation?

If there's a ftp task on the SSIS package, for example, FTP to say, D drive - does this mean it'll ftp the file to the user's workstation's D drive and not the server's D drive?
Post #1489494
Posted Thursday, August 29, 2013 11:12 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Exactly.

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1489809
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse