You are administering SQL Server Integration Services (SSIS) permissions on a production
server that runs SQL Server 2012.
Quality Assurance (QA) testers in the company must have permission to perform the
following tasks on specific projects:
View and validate projects and packages
View Environments and Environment variables
Execute packages
You need to grant the minimum possible privileges to the QA testers.
What should you do? (Each correct answer presents part of the solution. Choose all that
apply.)
A.
In the SSISDB database, add QA Tester logons to the ssis_admin role.
B.
In the msdb database, add QA Tester logons to the db_ssisoperator role.
C.
Grant Modify permission in the projects to the QA Tester logons.
D.
Grant Read permission in the SSIS catalog folder, the projects, and the Environments to
the QA Tester logons.
E.
Grant Execute permission in the projects to the QA Tester logons.
F.
In the msdb database, add QA Tester logons to the db_ssisItduser role.
B, D, E
just B and D.
db_ssisoperator
Enumerate all packages.
View all packages.
Execute all packages.
Export all packages.
Execute all packages in SQL Server Agent.
Min, and what about E?
Question is confusing. Question mentioned that
View and validate projects and packages
View Environments and Environment variables
Execute packages
Apperently , db_ssisoperator role of 2005,2008,2012 have no operation to perform validate projects , view Environments and variables.
Enumerate all packages.
View all packages.
Execute all packages.
Export all packages.
Execute all packages in SQL Server Agent.
http://benchmarkitconsulting.com/colin-stasiuk/2009/05/01/running-ssis-packages-security/
https://technet.microsoft.com/en-us/library/ms141053(v=sql.110).aspx
because of using Environment variables and Projects ,supposed that its SSIS Catalogue.
Then what have we do with MSDB?
Prior to SQL 2012 and SSISDB all packages are stored either in MSDB or in file system.
Since 2012, packages are stored in SSISDB.
I got second though after reading this post
https://www.mssqltips.com/sqlservertip/3153/managing-ssis-security-with-database-roles/
Answer should not be this?
A.
In the SSISDB database, add QA Tester logons to the ssis_admin role.
D.
Grant Read permission in the SSIS catalog folder, the projects, and the Environments to
the QA Tester logons.
E.
Grant Execute permission in the projects to the QA Tester logons.
what do you think of it? We need to validate this Q and verify if provided answer is correct.
We know that db_ssisoperator handles Execute All Packages so no need for E.
B and D I think:
View and validate projects and packages – db_ssisoperator
View Environments and Environment variables – Read permission in the SSIS catalog folder etc.
Execute packages – db_ssisoperator
Except that I can’t find anything on Validate Projects…
I vote B & D.
The question is ab it weirdly phrased but the gust of it comes down to B & D