September 19, 2014 at 2:00 pm
Is there anyway to disable the Deploy Feature within VS/SSDT 2012? We (DBAs) are using VS to deploy Projects (Packages) to our SQL Server (Integration Services Catalogs). We do not want the Developers to try to deploy their own Projects (Packages) from within VS/SSDT. (We are using Project Deployment Model.)
What are some of the methods to deploy a Project (Package)? I have found the following. I have used the first method but not sure about how to use some of the other methods expecially number 3, 4, and 5. What is the best (from change management perspective) to use? Are there other methods?
1. To start the Integration Services Deployment Wizard from an open project in Visual Studio, select Deploy from the Project menu.
2. To start the wizard in SQL Server Management Studio, expand the Integration Services Catalogs > SSISDB node in Object Explorer, right-click the Projects folder, and then click Deploy Project.
3. Using Stored Procedures.
4. Deployment Utility.
5. Package Installation Wizard.
Thanks in advance, Kevin
September 20, 2014 at 2:40 am
Have you considered setting the security on SQL Server so that these devs do not have the rights to deploy to your selected server(s)?
Sounds much easier (and more achievable) than what you are trying to do.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 3, 2014 at 7:16 am
Thanks Phil. Could you share how to set the security on SQL Server so that the developers do not have the rights to deploy to the sql servers? I found the following information but not sure if this is the way to go to keep developers from deploying to sql server.
Managing SSIS Security with Database Roles
http://www.mssqltips.com/sqlservertip/3153/managing-ssis-security-with-database-roles/
SSIS Database Role
If people want to access the catalog, they need to be added to the SSISDB database. The first step in setting up security is assigning the correct groups/users to database roles. There is only one database role however: ssis_admin. Users belonging to this role can do pretty much anything in the catalog, except dropping it. There is not much documentation about this role, but you can check the securables of this role in the properties window:
Obviously, sysadmins have also full privileges in the catalog. If you want to give users less permissions, you add them to the SSISDB database, but you don't assign them to any role (implicitly they are added to the public role). As with all database security, it is advised to work with groups and not with individual users.
This user can now log into the SSISDB database, but he cannot see the internal tables. He can see all of the catalog views, but they won't return any data (more on that later).
SSIS Permissions
If we want to give users appropriate permissions (without just adding them to the ssis_admin role), we'll need to assign them to the correct securables. The catalog has three securable objects: projects, environments and packages.
Under Integration Services Catalogs, under SSIS DB, right click the Folder, select Folder Properties, click Permissions Page, select Logins/Roles, and grant the Permssion.
Thanks, Kevin
October 3, 2014 at 7:43 am
kevinsql7 (10/3/2014)
Thanks Phil. Could you share how to set the security on SQL Server so that the developers do not have the rights to deploy to the sql servers? I found the following information but not sure if this is the way to go to keep developers from deploying to sql server.Managing SSIS Security with Database Roles
http://www.mssqltips.com/sqlservertip/3153/managing-ssis-security-with-database-roles/
SSIS Database Role
If people want to access the catalog, they need to be added to the SSISDB database. The first step in setting up security is assigning the correct groups/users to database roles. There is only one database role however: ssis_admin. Users belonging to this role can do pretty much anything in the catalog, except dropping it. There is not much documentation about this role, but you can check the securables of this role in the properties window:
Obviously, sysadmins have also full privileges in the catalog. If you want to give users less permissions, you add them to the SSISDB database, but you don't assign them to any role (implicitly they are added to the public role). As with all database security, it is advised to work with groups and not with individual users.
This user can now log into the SSISDB database, but he cannot see the internal tables. He can see all of the catalog views, but they won't return any data (more on that later).
SSIS Permissions
If we want to give users appropriate permissions (without just adding them to the ssis_admin role), we'll need to assign them to the correct securables. The catalog has three securable objects: projects, environments and packages.
Under Integration Services Catalogs, under SSIS DB, right click the Folder, select Folder Properties, click Permissions Page, select Logins/Roles, and grant the Permssion.
Thanks, Kevin
You've done your homework, I see. I am not a DBA and do not claim particular expertise in this area, however I do remember a DBA colleague getting frustrated with the SSIS_Admin role for the reasons you mention – it's a blunt instrument. The link that you have provided is about the best documentation of this dark area. I think you should dive in and do some experimentation. Or behave like a DBA and just take your devs out of the SSIS_Admin role to completely torch their rights in SSISDB 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply