Permission issue with dts package

Question

Permission issue with dts package

Russel Loski

SSCertifiable

Points: 7058
More actions
December 21, 2006 at 2:11 pm

#69371

I have a DTS package that one of the developers wants to run. It will delete a table then recreate it. Then he will load that table from a file that he will have put in a specific folder. The table must be owned by dbo. He wants to launch the DTS package when he gets the file.
My first concern is giving him as little permission as possible. I would prefer, especially since I am an interim dba until they hire someone (I'm a contractor) not to add him to dbowner role. I would even like to avoid making him a ddladmin in that database even if that would meet our goal.
I created a script that will read a table and launch each job in that table that has a "RunJob" bit set (there is more to it, but I think this is enough to get the picture). This developer would call a stored procedure that would set the bit for the specific job that would run the dts package.
I would schedule a job to run that script every minute.
An alternative I have considered is to use a SQL login in the DTS package connection that is a dbowner.
Can anyone think of a downside to this plan? Is there another way for the developer to run the DTS package within the security constraints I have set.
Russel Loski, MCSE Business Intelligence, Data Platform

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Bert Carles SSCrazy Points: 2634 More actions · Answer 1

I got around this security concern by creating a SQL Server login with SA authority. Then create a .bat file which runs the dtsrun command line utility (encrypted) using this login. The developer can run the .bat file at will.

Take care,

Bert

"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday

Russel Loski SSCertifiable Points: 7058 More actions · Answer 2

I had experminented setting the user name and password as an argument in the dtsrun command. The sql statements in the package still ran as the domain login of the person who ran that dtsrun statement.

Russel Loski, MCSE Business Intelligence, Data Platform

Bert Carles SSCrazy Points: 2634 More actions · Answer 3

You may need to change the owner of the DTS package?

Take care,

Bert

"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

Russel,

I did what you describe in your original post. The user sets a bit in a table, a scheduled job reads the table and enables and starts a job that runs the DTS package. This works security-wise because scheduled packages run as the SQL Server Agent login which, in our shop, is a domain account and member of the sysadmin server role. The user only needs permission to run the stored procedure that updates the bit in the table.

Greg

Russel Loski SSCertifiable Points: 7058 More actions · Answer 5

Greg, do you find significant overhead in running the scheduled job or was it rather trivial? I was thinking of scheduling this to check the table every minute. So a web app could set the bit so that a specific job would run when the schedued job runs. People would only have to wait one minute for it to start.

Russel Loski, MCSE Business Intelligence, Data Platform

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

My job checker runs every 10 minutes and it's so quick that I haven't noticed any slowdown. Mine actually decides which of 6 different DTS packages should be run by querying the table.

Greg

steve block SSCertifiable Points: 7698 More actions · Answer 7

Have you tried granting create table permissions to the user? You can grant permission to create a table within a database with the statement

GRANT CREATE TABLE TO User

This also works for a role (just add the user to the role).

From Enterprise Manager, right click on the database, select Properties, choose the permissions tab and check the box under the Create Table column for the User or Role.

This does allow them to create any table they want in the database.

Steve

Russel Loski SSCertifiable Points: 7058 More actions · Answer 8

Steve, thank you for your information. I had not thought to assign create table permission. In fact, I think that I had downright forgot about that.

It might work for us. Does that allow a user to drop tables as well?

Russel Loski, MCSE Business Intelligence, Data Platform

steve block SSCertifiable Points: 7698 More actions · Answer 9

From BOL:

If the CREATE TABLE statement is executed by a login associated with a user ID that has only create table permissions, owner must specify the user ID associated with the current login.

I would assume that if the user owns the table they can delete it but I don't know.

Steve

Greg Charles SSC-Forever Points: 45403 More actions · Answer 10

Russel,

In your original post, you said the table must be owned by dbo. That would preclude the user from dropping it without being a member of the DDLAdmin or db_Owner role. However, if the package is run by a scheduled job, I believe the table would be created and dropped by the login that SQL Server Agent runs as. If that login is a member of sysadmin, there wouldn't be a problem. See my previous post regarding this.

Greg

Russel Loski SSCertifiable Points: 7058 More actions · Answer 11

Running the query as dbo without giving dbo permission was one of my goals. Thus, Greg, that is why I am more inclined to your solution.

Russel Loski, MCSE Business Intelligence, Data Platform