1

I installed SSIS a couple of months after installing SQL Server 2014. I granted permissions to users per MSDN and rebooted SSMS and SSIS. I can login remotely from my client without issue, but have sysadmin rights.

The other user cannot even see instance when trying to log in via client Only way for her is to log in remotely to server, right click as administrator.

Any thoughts or suggestions on why this is?

Edit

It's a developer creating stored procedures and wants to store packages. There is going to be approximately 50 packages when complete with this project. She uses SSIS. I'm not that familiar with SSIS at this time.

  • Please add the MSDN link you referenced for granting permissions. – Shawn Melton Dec 11 '14 at 16:40
  • Can you explain what task you are trying to grant the user(s) permissions to do? Not sure what purpose there is with granting any user permission to just the service. Are you possibly meaning to grant them IS roles? – Shawn Melton Dec 11 '14 at 16:46
  • It's a developer creating stored procedures and wants to store packages. There is going to be approximately 50 packages when complete with this project. She uses SSIS. I'm not that familiar with SSIS at this time. – JRW71111 Dec 11 '14 at 16:51
3

Permissions to assign her are going to be based on what deployment model she is going to use within your environment. SSIS 2012/2014 added an additional model: Project and Package

For Package Deployment model she has the option of deploying the packages to msdb. Granting her permissions for this type of deployment you will grant her permissions to an SSIS role. If she is the only person that is going to be working with the packages you can simply grant her db_ssisltduser. This will provide her permissions to do anything she needs with the packages she creates and deploys (making her the owner of those packages). You can read through the article linked to get an idea of more.

For Project Deployment model you will need to work with the SSISDB catalog. With this it is just granting permissions at a database-level role. If this is not already created on the instance you will need to do this for her. Once it is created you should see a database that will have a specific role called ssis_admin, this role is special to the catalog database and is not a normal role found in every database.

The database will show up under the Databases node in Object Explorer, just go to Security>Roles>Database Roles and find the ssis_admin role. Within the properties of that role you can add her login. enter image description here

  • I have indeed granted her the permissions you mention and why I'm lost on why she cannot connect to SSIS from her client. – JRW71111 Dec 11 '14 at 17:23
  • Thank you for the response and correction on how I ask the question. I will consider more when I post a question to ensure I include everything. – JRW71111 Dec 11 '14 at 17:23
  • For a project deployment scenario, SSISDB has a specific ssis_admin role that is probably more suitable than giving them owner – billinkc Dec 11 '14 at 21:34
  • @billnkc thought there was something equivalent to roles used with msdb for the catalog. That ssis_admin is hidden inside BOL. – Shawn Melton Dec 11 '14 at 21:41
  • BOL? Ain't nobody got time for that (I had to request the role yesterday or I'd have forgotten about it) – billinkc Dec 11 '14 at 21:44
0

I had a similar issue where I still got the same "access denied" error, even after editing the DCOM permissions as described in the linked MSDN post.

In my case, the solution was to add the domain account in question to the local Distributed COM Users group on the SSIS host server.

The DCOM permissions were still necessary, just not (in my case) sufficient.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.