Permission problem running a DTS froma stored procedure 1

[GJP55]

I have a stored procedure that calls a job which is a DTS. If I run this with my login (DBA) then it works fine but when I run this as a user I get an error which says it cannot find the job I am calling. I have assigned permissions to use the Stored procedure, also on the tables that the dts effects (delete/update/select/insert).
I am not sure if the problem is permissions to run the job or permissions to run the DTS (if there are either that require that).
Can anybody help?
Many thanks

 

[nigelrivett]

What do you mean by calls a job? Starts a scheduled task?

Where is the package held? If in msdb then you will probably need permission on sysdtspackages.

You need to give a bit more info about what you are doing.
When the package runs it will get a connection to the server and your user eprmissions probably won't affect that.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[GJP55]

Sorry, didn't make it too clear.
I have a DTS that imports data from a spreadsheet. To run the DTS I have created a scheduled job and a stored procedure activates the Scheduled job when a User clicks on a button from an Access Front End.

Does this make it clearer ?
Thanks

 

[nigelrivett]

I'm guessing it runs sp_start_job.
If the user doesn't own the job then they will have to be sysadmin.

You can get round this by adding an entry to a table and having a job look at that table to start the package.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[GJP55]

Yes, it is sp_start_job.
Could you explain what you mean when you say adding an entry to a table and have a job look at the table ?
Thanks for your help.

 

[nigelrivett]

The table is

create table execsq
(
id int identity ,
sql varchar(1000) ,
status int default 0 ,
insdate datetime default getdate() ,
rundate datetime default '19000101'
)

Now your SP just indeserts the dtsrun command into this table instead of starting the job.

You have a scheduled job running every minute which gets the next entry from the table with status 0, sets the status to 1, executes the dtsrun command then sets the status to 2 on completion.

Now the user just needs permission on the sp which inserts into the table and you can execute the dts package as the the scheduled job owner.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[GJP55]

You would have thought SQL would have built in something to make the job easier to do rather than having to find a work around like your solution.
But thanks very much for your fix and spending time to help me.
Cheers

 

[nigelrivett]

>> You would have thought SQL would have built in something to make the job easier to do rather than having to find a work around like your solution.

You are trying to circumvent the security. Allow someone to do something which they don't have permission to do. It's not going to implement something to make it easy to break security.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[GJP55]

I was thinking more along the lines of being able to assign permissions to run a specific DTS.

 

[GJP55]

I have set this up now and it works fine. Only problem with this is that the User would have to wait up to 60 seconds from clicking the import button to the job actually running.
Is there no other way to get this to run straight away ?

 

[nigelrivett]

Well you can use dtsrun from an SP and not bother with the job.
You can give users permission to execute xp_cmdshell.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Kalpha7]

I like the idea of a continuosly executing job that scans a user-defined table for the next actions to be performed. It's a very useful method that I've used on several occasions. The drawback with this method is that it circumvents secuirty completely. It is the equivalent of granting "SA" privileges to every DTS/job that is executed by the automated job. Instead of adding the automated job and additional datasource layers - just grant "SA" to everybody and avoid the extra work. It's the same thing.

In situations where the execution of a package or job is initiated by a GUI there's a much simpler solution.

 

[rajkum]

Is it appropriate to ask here??

But I am trying to do it thru ASP?

I have posted a question though?