none
SSIS Catalog difference between granting someone SSIS_Admin or db_owner permissions RRS feed

  • Question

  • Situation:

    I have a group of SSIS administrators who should be able to:

    The requirement states that All ETL administrators must have full privileges to administer and monitor the SSIS catalog, and to import and manage projects.

    I'm not sure if the new "SSIS Admin" is the best solution for my problem.

    Problem:

    I created a sql server login and mapped the SSISAdmin group (windows server) to the sql server login (convenient I called it also SSISAdmin).

    I mapped the SSISAdmin sql server login to the SSISDB Catalog database.

    Now I'm considering whether the SSIS_Admin of the SSISDB database will suffice or should I consider taking the db_owner role? I'm not sure or don't find a clear answer on the permission differences between the two database level roles, except that they both state that the roles allow the user to do "anything & everything" on the database. The first thing that comes to mind is that I don't want the SSISAdmins to be able to delete the entire SSISDB database (db_owner)! Will the SSIS_Admin role prevent this... What are the most profound differences between the two and why did Microsoft introduce this role specifically on the SSIS Catalog.

    Thank you

    Thursday, March 21, 2013 11:15 AM

Answers

  • The first place that I would start is to look at the Securables tab on the ssis_admin role.  Basically, the members of ssis_admin can execute several stored procedures and functions in the catalog schema and not much else. A member of SSIS_admin will not be able to delete the SSISDB databse.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Jinxlucky Friday, March 22, 2013 12:03 AM
    Thursday, March 21, 2013 12:13 PM

All replies

  • The first place that I would start is to look at the Securables tab on the ssis_admin role.  Basically, the members of ssis_admin can execute several stored procedures and functions in the catalog schema and not much else. A member of SSIS_admin will not be able to delete the SSISDB databse.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Jinxlucky Friday, March 22, 2013 12:03 AM
    Thursday, March 21, 2013 12:13 PM
  • Thx R. Loski, thx for your input

    I looked at them indeed before I posted the question.

    Earlier today (to be correct yesterday), I read somewhere that the only role that has specific manage permissions for the SSIS Catalog database is the SSIS_admin role. Even though the db_owner role states that you can do about anything on a database. I did look at the Securables like you said and must agree with you... Yet still not very clear on the "why" of this unique role for the SSISDB. 

    I also agree on the delete database capability of the db_owner, in concurrence to the SSIS_admin role.

    Still not found an article or something in the msdn library that gives me a clear understanding of the difference between the two or better yet, the need to choose one over the other. For now I think the SSIS_Admin will do the trick for me. I notified the SSISAdmin members to notify me as soon as they encounter certain permission problems when managing the SSISDB_Catalog or any projects within it.

    Once again thx, appreciated

    Friday, March 22, 2013 12:03 AM