Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Olde_DBA
Starting Member
3 Posts |
 Posted - 2009-02-11 : 14:59:59
|
| Hello:I’m hoping for help in administering SQL Server 2008 packages in a multi-database (each with different programming teams) instance.We’re storing the packages in the msdb database.When I say “look at their packages” I mean within the SSIS connection of Management Studio the ability to select a package, select “run”, then look at the connection managers, etc.1) Ideally what I want is in the test and production environments the programming teams to have the ability to look at *their own team’s packages*, but *not modify or execute* them. Unfortunately db_ssisadmin lets them look at and run any packages (can delete any too), db_sysoperator allows them to look at and execute any packages, db_ssisltduser only allows them to view their personal packages (not all the members of their programming team) and execute, delete them.It looks like even if I can figure out how to use the readrolesid column in msdb.dbo.ssispackages they are still going to be able to execute the packages.So I’d truly appreciate any help from someone who’s accomplished this. Has anyone figured out a way to implement this?2) A second question is how are you supposed to use the readrolesid column in msdb.dbo.ssispackages. Given that it’s a variable binary it’s not the name of a SQL Server role you define? Where do you find the roles “id”? Are you supposed to just update it barefoot or is there a stoproc you call to safely update it?3) Same question as number 2 but about the “ownersid” column. Where do you find “The unique security identifier of the user who created the package”? Can you update this to be a SQL Server Role so we can allow a group of developers to look at the packages for their team?4) 4th question…. What good is the ability to create more “ssis folders” in the msdb? It doesn’t look like you can specify one to store a package when you use the installation wizard to import one from it’s “.manifest” file. Once you do import one into the default folder you don’t seem to be able to move it into another folder. Thanks in advance...Ye Olde DBA |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-02-12 : 06:30:41
|
| Have you thought about holding them in files?Then you can give ead access to the folder (and give a folder per team) so they could load the packages but not alter them.If a user can view the package (I assume you mean in BI studio rather than the xml) then as it's a client applcation then they can run them lbeit on their client.If you just want to stop them being run on the server then don't give them access to the server - just read access to the file share.If you really want to store them in msdb (I don't advise that as it makes things a lot more difficult) then write something to extract the files for them.Release would then be copying the .dtsx file to the folder (from your source control system).Note you then don't have to give access to the version on the server just to what's in source control.I think this probably gets round most of your questions as you're not restricted by msdb storage options.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Olde_DBA
Starting Member
3 Posts |
Posted - 2009-02-12 : 08:09:11
|
| Thanks for the reply nr. We'll consider that.Does anyone have any input on my questions about using the MSDB database for storing the SSIS packages instead of the file system?Ye Olde DBA |
|
|
|
|
|
|
SSIS 2008 Multi-databae package security ?'s