Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 DTS package to SQL permissions...

Author  Topic 

mrpush
Starting Member

14 Posts

Posted - 2013-03-15 : 13:29:32
Hi,

I'm running a DTS package that creates a CSV file by using 2 temp tables that are created and then dropped.

I run the DTS from a batch file, that runs the DTSRUN utility on a users workstation.

It errs on creating the temp files unless I set the current SQL LOGIN user permissions to "SYSTEMS ADMINISTRATOR" under sever roles, which I DO NOT WANT for these workstation users.

I looked at set permissions like create, set, update etc under DATABASE ACCESS, however I can't chose a TEMP database for access that does not exist or is created and then dropped!

Also, with certain permissions, the DTS will create the temp table, but then it will assign the workstation user as the OWNER and then the DTS will not run because of that.

Note these temp tables and not GLOBAL temp tables (not stored in tempDB) they are created and dropped within my SQL db.

Any ideas here on where and what to set? I just can't figure it out.

Thanks much,

Mark

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-03-16 : 13:56:44
Anyone can create temp tables. No special permissions are required. And all temp tables are created in tempdb.

See here and scroll down to Temporary Tables

Might also look at this article by Graz from back in the day when we were all still using SQL 2000
Go to Top of Page
   

- Advertisement -