How to limit access to DTS packages

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dave4dl

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.

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.

dave4dl

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?

ASKER

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

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.

SOLUTION

dave4dl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

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?

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.

If someone is going to go to the trouble of deleting older versions, trust me you have bigger problems...

ASKER

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?

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 :-))

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

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.

I give up.

acperkins: Don't do that. We compliment each other. :-)

ASKER

Thanks to both of you. I actually got littlie in-depth info that I didn't know nor was I considering.