| Author |
Topic |
|
Peter Cwik
Starting Member
12 Posts |
 Posted - 2009-01-06 : 14:15:41
|
| I have a user that will be interactively (not scheduled) excuting an SSIS package. Besides obvious access to the databases that the package will read/write to, what access is required to allow a joe-blow user to execute an SSIS package? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-06 : 14:20:06
|
| You can this to control SSIS jobs.http://technet.microsoft.com/en-us/library/ms188283.aspx |
 |
|
|
Peter Cwik
Starting Member
12 Posts |
Posted - 2009-01-06 : 14:26:29
|
| No. This is why I specifically stated that the job would run INTERACTIVELY not scheduled, thus SQLAgent does not come into play. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Peter Cwik
Starting Member
12 Posts |
Posted - 2009-01-06 : 14:43:01
|
| Why did you do that? No one answers topics that are already replied to. That is why I opened a similar topic without the incorrect answer. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Peter Cwik
Starting Member
12 Posts |
Posted - 2009-01-06 : 14:47:09
|
| So can you answer my question? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Peter Cwik
Starting Member
12 Posts |
Posted - 2009-01-06 : 15:00:22
|
| I have. It didn't work. That's why I posted the question. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Peter Cwik
Starting Member
12 Posts |
Posted - 2009-01-06 : 21:11:02
|
| I'm not doing 20 rounds with you.The question is...What permissions are REQUIRED to let a user INTERACTIVELY execute an SSIS package?I would think this is a straight forward question, with an answer like...A user must be a member of X role to execute an SSIS package interactively. But so far all research has only spoken of permissions required to execute a package that is scheduled and using SQLAgent. I don't care about SQLAgent or scheduling the package. The error I'm getting is not important, IF you know the answer to original question. I know you are trying to help, but I don't need guidance, I need 1 correct answer to 1 question. If you don't know the answer please don't respond. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Peter Cwik
Starting Member
12 Posts |
Posted - 2009-01-21 : 16:12:26
|
| Answer: To configure rights for remote users on Windows Server 2003 1. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.2. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.3. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.4. Right-click on MsDtsServer and select Properties.5. In the MsDtsServer Properties dialog box, select the Security tab.6. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.7. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.8. Click OK to close the dialog box.9. Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.10. Close the MMC snap-in.11. Restart the Integration Services service.Once the above is completed, in SQL Server in the MSDB database give the user access to the database role db_dtsoperator .One step to add to that. The user must also be added to the DCOM users group on the machine which SQL Server in installed. I have created a group called SSIS_Operators which I provided access to DCOM Users and the DCOM Config Permissions Pete listed below. All we need to do for additional users is add them to this security group. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-21 : 19:51:51
|
Thanks for information. |
|
|
|
sonomaRIK
Starting Member
2 Posts |
Posted - 2011-05-12 : 13:54:13
|
| That was an excellent breadcrumb like post. Just what one needs in a pinch and what I would have done.Once the above is completed, in SQL Server in the MSDB database give the user access to the database role db_dtsoperator ^THIS IS the only thing you had to do to make this happen, all the other DCOM etc. steps are un-necessary.Remmember also, that MS SQL 2008 the role name changed to DB_SSISLTDUSER: Operator role serves only to casual operator to run the package, but ..USER allows modifications and scheduling IF you also include SQLAgentOperatorRole SQL 2008SO: MSDB --> Security --> Roles --> db_ssisltduser --> add the user/group name --> check the two boxes related to DB_SSISLTDUSER [probably already done] and SQLAgentOperatorRole if you want them to schedule it, and it's good to go. |
|
|
|
sonomaRIK
Starting Member
2 Posts |
Posted - 2011-05-12 : 13:56:01
|
| almost forgot do NOT give that grant of Administrator / ssisadmin to anyone other than sqladmin. it would allow the user to change any/all packages not even truly belonging to them. |
|
|
|
|
Minimum Permissions to execute SSIS