There are two parts to securing the SSIS Catalog. They are both simple, but one is a little obscure if you like to jump into things without reading the documentation.
Typically, we like to add users to a role when we grant them access to a database. This is not the case with the SSIS Catalog unless you need (note: I said need as in really need) to give someone full control to your catalog. For typical users, you'll just want to add them as a user in the SSISDB database without adding them to a role. They get basic access with the public role and that should be all they need. More granular permissions are configured in the next step.
Granting permissions on the SSIS Catalog objects is quite simple. You can do it through SSMS or via T-SQL though I don't see the point in going the T-SQL route unless you're scripting a task. Even then I think I would use PowerShell and SMO.
To set permissions just right-click on the object you want to set the permissions on (a folder, project, or environment) and choose Properties... From there, select the Permissions tab and you'll get a dialog box like the one shown below.
I chose the guest account for illustration purposes only |
permission_type Value | Permission Name | Permission Description | Applicable Object Types |
---|---|---|---|
1 | READ | Allows the principal to read information that is considered part of the object, such as properties. It does not allow the principal to enumerate or read the contents of other objects contained within the object. | Folder, Project, Environment, Operation |
2 | MODIFY | Allows the principal to modify information that is considered part of the object, such as properties. It does not allow the principal to modify other objects contained within the object. | Folder, Project, Environment, Operation |
3 | EXECUTE | Allows the principal to execute all packages in the project. | Project |
4 | MANAGE_PERMISSIONS | Allows the principal to assign permissions to the objects. | Folder, Project, Environment, Operation |
100 | CREATE_OBJECTS | Allows the principal to create objects in the folder. | Folder |
101 | READ_OBJECTS | Allows the principal to read all objects in the folder. | Folder |
102 | MODIFY_OBJECTS | Allows the principal to modify all objects in the folder. | Folder |
103 | EXECUTE_OBJECTS | Allows the principal to execute all packages from all projects in the folder. | Folder |
104 | MANAGE_OBJECT_PERMISSIONS | Allows the principal to manage permissions on all objects in the folder. | Folder |
If you want to give someone access to view the SSIS Catalog reports but not do anything else in the database, I am afraid that you are out of luck. As described in this Microsoft Connect case, it didn't make it into RTM: SSIS Catalog doesn't have option to give read access to SSIS Catalog to view package run reports. If a new case pops up I will post a link to it.
If you have more information to share please post a comment!
No comments:
Post a Comment