You are administering SQL Server Integration Services (SSIS) permissions on a production
server that runs SQL Server 2012.
Package developers in your company must have permission to perform the following tasks
only on their own projects:
View projects and packages View Environments Validate packages Execute packages
You need to grant rights to the developers without assigning unnecessary privileges.
What should you do? (Each correct answer presents part of a solution. Choose all that
apply.)
A.
Add developer logins to the db_ssisltduser role in the msdb database.
B.
Add developer logins to the db_ssisoperator role in the msdb database.
C.
Grant Execute permission in the projects for the developer logins.
D.
Grant Read permission in the SSIS catalog folder, the projects, and the Environments.
E.
Add developer logins to the ssis_admin role in the SSISDB database.
F.
Grant Modify permission in the projects for the developer logins.
Explanation:
B: db_ssisoperator
* Read actions
Enumerate all packages.
View all packages.
Execute all packages.
Export all packages.
Execute all packages in SQL Server Agent.
* Write actions
None
D: Need read permissions on the Environments in order to be able to view them.
Incorrect:
Not A: db_ssisltduser
* Read actions
Enumerate own packages.
Enumerate all packages.
View own packages.
Execute own packages.
Export own packages.
* Write Actions
Import packages.
Delete own packages.
Change own package roles.
Not E: db_ssisadmin
Too many permissions (such as delete all packages).
Question says: …perform… on own… which is db_ssisltduser
From MSDN:
SQL Server Integration Services includes the three fixed database-level roles, db_ssisadmin, db_ssisltduser, and db_ssisoperator, for controlling access to packages. Roles can be implemented only on packages that are saved to the msdb database in SQL Server. You assign roles to a package using SQL Server Management Studio. The role assignments are saved to the msdb database.
db_ssisltduser
Enumerate own packages.
Enumerate all packages.
View own packages.
Execute own packages.
Export own packages.
Import packages.
Delete own packages.
Change own package roles.
To me it is C,D.
SSISDB is a relavant DB for project mode.
C and D
Not A an B, because this options to msdb DB that not support PROJECT, Environments and e.t.
Not E and F – too many permissions
http://blogs.msdn.com/b/mattm/archive/2012/03/20/ssis-catalog-access-control-tips.aspx
C and D is the correct answer.
Correct answer should be A & C.
The Question says,Developers can Read,Validate,Execute only their own projects(db_ssisltduser) checkout this link;
https://msdn.microsoft.com/en-us/library/ms141053(v=sql.110).aspx
PLEASE IGNORE MY PREVIOUS COMMENTS! I have made corrections below. I had overlooked the fact that db_ssisltduser role includes rights to execute own packages, therefore it is redundant to pick (C) since it’s covered by the assigned role.I have also add a 2nd link,please refer to the section about Managing Permissions.
CORRECTIONS; The answer should be A & D.
The Question says,Developers can Read,Validate,Execute only their own projects(db_ssisltduser) checkout this link;
https://msdn.microsoft.com/en-us/library/ms141053(v=sql.110).aspx
https://msdn.microsoft.com/en-us/library/hh479588(v=sql.110).aspx
A & D
Your correct cao, thanks for your insight.
I don’t believe cao is right, and he usually is. There are two major repositories for projects on sql server. MSDB and ssisdb. The former is for packages only and the latter is for projects only, though it should be noted that packages are contained within projects.
db_ssisltdusr is for msdb and grants read and execute for and on owned projects only
Read and execute priviledges is granted on an object by object basis on ssisdb.
So if the question is restricted to packages only that have not been deployed as projects, Answer A would be the answer. They would have to be deployed with dtutil or something. If the question was restricted to ssisdb, c & d would be necessary because reading does not imply execution in ssisdb like it does in the msdb. Here is the wording of the question:
“Package developers in your company must have permission to perform the following tasks
only on their own projects:”
This would imply the ssisdb choice which is c&d
But then it follows with:
“View projects and packages”. Now is that packages that are only stored and deployed within projects or packages generally. I believe it is the former and thus the answer is C&D. If it were the former, the answer would be A,C&D. This is a question of language construction and not of ssis. Once again, a crappy question that leads us to question construction rules on the English language.
C&D
db_ssisltdusr is for msdb and grants read and execute for and on owned projects only
should read:
db_ssisltdusr is for msdb and grants read and execute for and on owned packages only
If it were the former, the answer would be A,C&D
should read
If it were the latter, the answer would be A,C&D