How to limit access to DTS packages

Hi Experts,
We'd like to grant read-only access to somebody who should not be able alter any data on that server including DTS packages. I know if I setup read-only access to all the databases he will still have access to DTS packages as that not DB components.
I want him to be able to look at the dts packages open connections and all but not be able to change anything or save anything.
Please Advice with little details thanks
Rainbow002Asked:
Who is Participating?
 
dbbishopCommented:
If he can open the package and open connections within it, he can execute it, change it, even delete it.
You can set an owner and user password, so that the user can execute a package but not make changes to it without the owner password, but if you want them to be able to look at the package, they will need to have the owner password.
0
 
dave4dlCommented:
Authenticate into sql server using windows integrated authorization.  
Find the physical file storing the package.  
Set the OS permissions on that file to "read only" for that particular user.
0
 
dbbishopCommented:
Of course, this is assuming you've saved the package as a structured storage package. However, if they have Enterprise Manager, which they would have to have to view the package, there is nothing to keep them from making changes, running those changes, and even saving the package to a different directory, or directly to SQL Server.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dave4dlCommented:
what is the reason you would like them to view the dts package but not be able to edit?  Maybe there is another way to skin this cat that we can help you find?
0
 
Rainbow002Author Commented:
We want this person to do some documentation where he will write technical document including:
how many processes are running.
What objects are moving from what source to what destination (fully qualified)
What is the process of the move, (such as truncate and copy/transform or copy object)

For this, He'd have to open packeges and drill down to connections and tasks etc...But we do not want him to accidently change something or delete which has happened once.

Any suggestions will be greatly appriciated
0
 
dbbishopCommented:
I'd save all the packages as structured storage packages and store them somewhere he does not have access to. Allow access and if something happens, you can restore them. The only other option I see is set up an MSDE on another compute and copy the database over to it. Allow him to access that copied database on a different computer.
0
 
dave4dlCommented:
i would suggest making a copy of the dts package and have him document the copy
0
 
dbbishopCommented:
If he is able to read VB Script proficiently, you could do what dave4dl suggests by saving the package as a Visual Basic file. It will show all connection info, transformations, etc.
0
 
Anthony PerkinsCommented:
I would suggest you use the version control system already built into SQL Server and not get more creative than that.

You do realize that every time a package is modified a new version is created, keeping the old one intact, right?
0
 
dbbishopCommented:
But, if the user has full access and they've had problems sith someone deleting packages in the past, there is nothing to keep them from doing it again. Backing up the msdb database (where packages are stored) will also provide some protection as it can be restored if womething goes awry.
0
 
Anthony PerkinsCommented:
If someone is going to go to the trouble of deleting older versions, trust me you have bigger problems...
0
 
Rainbow002Author Commented:
Thanks guys for your input. Is there a store proc that I can deny insert/delete etc.. so that user can open the package look at it and if tries to save it, can't because of insuficcient rights?

There's this sp sp_enum_dtspackages if I deny exec on it user wouldn't be able to see any dts at all. I'm looking for something similar?
0
 
dbbishopCommented:
acperkins: Don't you know by now that, if it can be done, it will be done. That's as much a fact as gravity, taxes and death! You accidently delete the newest version instead of the oldest, you delete all versions, you right-click on the package to click All Tasks... but accidently click one down on Delete and answer Yes because you thought you were doing an Import, your boss didn't give you the raise you thought you deserved...

Rainbow002: Sorry, if you are going to let them open the package for any reason, they have full access to it-change, execute, delete. Options:

1) Move to a test server for documentation purposes (preferred).
2) You can modify a package to provide some limited control over executing it but not to prevent changes or deletions of a package. Add a global variable named test, with a value of 0 or 1. Add an Active-X script task as the first step. If test=1, then exit the package, otherwise, allow it to continue running.

(this job has created a monster-two months ago, I wouldn't have even known you could do something like that :-))
0
 
Anthony PerkinsCommented:
dbbishop,

>>Don't you know by now that, if it can be done, it will be done.<<
You are missing the point, but perhaps it is that I am used to working with professionals.

But why don't we focus on helping the questioner instead of coming up with a myriad reason why X or Y "doesn't work"

Rainbow002,
Take a look at this article, this appears to have the right solution for you:
Package Ownership Issues
http://www.sqldts.com/212.aspx
0
 
dbbishopCommented:
Thought I was. He'd made the comment,
>>For this, He'd have to open packeges and drill down to connections and tasks etc...But we do not want him to accidently change something or delete which has happened once.
0
 
dbbishopCommented:
The fact that there are different versions kept doesn't help if a package was changed inadvertantly and run after the change was made and 250,000 records were screwed up as a result, which wasn't discovered until a week later.
0
 
Anthony PerkinsCommented:
I give up.
0
 
dbbishopCommented:
acperkins: Don't do that. We compliment each other. :-)
0
 
Rainbow002Author Commented:
Thanks to both of you. I actually got littlie in-depth info that I didn't know nor was I considering.
0
 
Rainbow002Author Commented:
acperkins, the link you provided has decent info:
 http://www.sqldts.com/212.aspx

Again thanks to both you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.