Hi,

I have a sql server with authentication set to Windows only

I have a DTS pacakge that does a monthly update a some static data. I load
the data like:
-deletes the table
-create the table
-fill table with data

It's a big table so this is faster then delete the rows

I want the user to initiate the update of the data, so they need to execute
the package
The user has limited permissions, only exec on the needed stored procedures
and select on some views and tables.
If i run the package with
Flags:=DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_ UseTrustedConnection then
i get a permission error on creating the table

Is it possible to impersonate an other user for just the execution of this
package. I don't want to place any passwords on the client!!


How do I make it possible for the user to execute this package without
making this user db_owner of starting the package with adding a password in
the client

Marc

You will need a layer of abstraction which means either some form of proxy
that contains elevated credentials, or a disconnected method.

For the proxy you could look into programming some form of impersonation, or
the runas tool.

For a disconnected solution the user can update a flag in a database,
perhaps insert a row or update an existing value, which in effect that says
that the package should be run.You can then schedule a job to check for this
flag and then run the package if required. Whilst it will not be
instantaneous is a far better solution overall, and if setup by a sysadmin
the job will have full permissions on the server so the package can do
whatever you want.


--
Darren Green
http://www.sqldts.com


"marc" <heev (AT) msn (DOT) com> wrote