| Author |
Topic |
|
elancaster
A very urgent SQL Yakette
1208 Posts |
 Posted - 2004-10-01 : 07:24:40
|
| I want a colleague to be able to run DTS's for me. Nothing fancy just basic imports, updates and deletes. If they try and run them they fail because this person does not (and will not) have delete permissions on any of the data. Any ideas on how I can maybe use a role whereby the DTS can go do it's deletes etc when he runs it, without me actually giving him delete permissions? |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-01 : 07:36:29
|
| creat a package and set a user password for the DTS and share the password to him |
 |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2004-10-01 : 07:42:26
|
| nope, tried that. Still says it fails because delete permission is denied.Any other ideas? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-01 : 13:38:39
|
| You'll have to use stored procedures if you don't want the user to have delete permissions. With stored procedures, the user does not need explicit permissions on table, but rather just EXEC on the stored procedure. So create a stored procedure that contains the delete statement, grant exec to the user on it, then use the stored procedure in your DTS package.Tara |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-02 : 10:33:16
|
| just be sure the owner of the stored procedure has delete options. in my case, i don't allow sprocs to be owned by dbo, but rather by approles. there are a few for general use but for specific purposes, IMHO it's more secured. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-04 : 12:30:13
|
quote:
Originally posted by jen
just be sure the owner of the stored procedure has delete options.
Jen, that isn't what you want. You only need EXEC permissions on stored procedures UNLESS the stored proc is using dynamic sql.Tara |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-04 : 23:52:48
|
if your sprocs are owned other than the dbo, you need to have the permissions set to the sproc owner otherwise you'll get a permisssion error. and yes, this is what we need. we tried this before when we were still setting up security. an sproc owned by dbo and sproc owned by a user other than dbo, ofcourse the public role was stripped to it's minimum permission if not none at all.we found out that if a user executes dbo.sproc without proper permissions, it is able to perform whatever SIUD, but if its user.sproc then the user is restricted to the permissions set to its account. ofcourse owners were decided on the type of the process involved in the sprocs.i maybe wrong here as i'm still a beginner but the experiment was pretty clear to me. |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-05 : 02:04:11
|
quote:
Originally posted by elancaster
I want a colleague to be able to run DTS's for me. Nothing fancy just basic imports, updates and deletes. If they try and run them they fail because this person does not (and will not) have delete permissions on any of the data. Any ideas on how I can maybe use a role whereby the DTS can go do it's deletes etc when he runs it, without me actually giving him delete permissions?
Tha userid used inside the DTS package for the SQL server connection, should have the delete permission on the table; not the user executing the package.Hemanth GorijalaBI Architect / DBA... |
|
|
|
|
Security and DTS