HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Granting permissions to SSIS for developer

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
permissionsssisgrantingfordeveloper

Problem

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.

Solution

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.

Context

StackExchange Database Administrators Q#85937, answer score: 5

Revisions (0)

No revisions yet.