Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to limit access to DTS packages

Posted on 2007-10-01
20
Medium Priority
?
639 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:Rainbow002
  • 9
  • 4
  • 4
  • +1
20 Comments
 
LVL 15

Accepted Solution

by:
dbbishop earned 750 total points
ID: 19992820
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
 
LVL 15

Expert Comment

by:dave4dl
ID: 19992993
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 19993251
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 15

Expert Comment

by:dave4dl
ID: 19993283
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
 

Author Comment

by:Rainbow002
ID: 19993562
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 19993640
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
 
LVL 15

Assisted Solution

by:dave4dl
dave4dl earned 300 total points
ID: 19993644
i would suggest making a copy of the dts package and have him document the copy
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19993951
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 19996138
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 19996154
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20003852
If someone is going to go to the trouble of deleting older versions, trust me you have bigger problems...
0
 

Author Comment

by:Rainbow002
ID: 20006125
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 20006402
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 450 total points
ID: 20008351
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 20009504
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
 
LVL 15

Expert Comment

by:dbbishop
ID: 20009513
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20011479
I give up.
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 20011705
acperkins: Don't do that. We compliment each other. :-)
0
 

Author Comment

by:Rainbow002
ID: 20014260
Thanks to both of you. I actually got littlie in-depth info that I didn't know nor was I considering.
0
 

Author Comment

by:Rainbow002
ID: 20014474
acperkins, the link you provided has decent info:
 http://www.sqldts.com/212.aspx

Again thanks to both you.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question