Link to home
Create AccountLog in
Avatar of Rainbow002
Rainbow002

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of D B
D B
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of dave4dl
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.
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?
Avatar of Rainbow002

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
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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...
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
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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. :-)
Thanks to both of you. I actually got littlie in-depth info that I didn't know nor was I considering.
acperkins, the link you provided has decent info:
 http://www.sqldts.com/212.aspx

Again thanks to both you.