none
SSIS Package Reader and Writer roles confusion.

    Question

  • Hi,

    I am going through SSIS package roles and I am confused regarding the Reader and Writer roles that we can assign by right clicking a package. The MSDN (http://msdn.microsoft.com/en-us/library/ms141053(v=sql.100).aspx) defines clearly the three roles db_ssisadmin, db_ssisltduser and db_ssisoperator. I am consufed with the Reader and Writer roles selected on a package, what are they used for?

    From my testing, a user with role db_ssisoperator for example, will have access to the packages regardless of what is selected in the Reader Role list of the package.

    In the case of a user defined role "TestSSIS" assigned in the Reader Role of a package, a user without "TestSSIS" role but db_ssisoperator role will still be able to access the package.

    So I don't really see what we are configuring with the Reader and Writer roles, any clarification would be appreciated!

    Thanks.

    Thursday, February 09, 2012 12:43 PM

All replies

  • This MSDN article outlines the designation of the reader and writer roles http://msdn.microsoft.com/en-us/library/ms186568.aspx

    Arthur My Blog

    • Proposed as answer by Koen Verbeeck Tuesday, February 14, 2012 10:48 AM
    Thursday, February 09, 2012 7:13 PM
    Moderator
  • Hi Annthony,

    Please also refer to the following links about the topic:
    Security in SSIS: http://www.sqlmag.com/article/encryption2/security-in-ssis
    Security Overview (Integration Services): http://msdn.microsoft.com/en-us/library/ms137833.aspx 

    Please feel free to ask if you have any question.

    Thanks,
    Eileen


    Tuesday, February 14, 2012 8:55 AM
  • Thanks for you answers, I actually read these links during my research and it does not clarify my question.

    In this article, http://msdn.microsoft.com/en-us/library/ms141053(v=sql.100).aspx , it is mentioned :

    To access a package, a user must be a member of the user-defined role and the pertinent Integration Services fixed database-level role. For example, if users are members of the AuditUsers user-defined role that is assigned to a package, they must also be members of db_ssisadmin, db_ssisltduser, or db_ssisoperator role to have read access to the package.

    It is where I am confused, what is the point to create a user defined role then? Secondly, if we assigned AuditUsers on the reader role of the package, it will not prevent users NOT in AuditUsers role but in one of the three SSIS roles to access the package.

    Thanks,

    Anthony

    Wednesday, February 15, 2012 10:02 AM