September 27, 2004 at 8:25 am
We have a new production environment we are moving our databases to, the maximum permissions any user will have is:
Tables: Select;Insert;Delete;Update - although securtiy will be set through the sps
SPs: Execute
Views: Select
UDFs:Execute
We use DTS to load our data, the strategy we want is to allow only execute on sps;udfs and select on views.
The problem is the connection in the package would require the connecting user to have insert and delete rights directly on the table which I need to get around.
Is there any way to some how do this through a stored procedure such as Select Into <TblName> From '<Path>' without using bcp or BULK INSERT (as requires BULK INSERT ADMINISTRATORS role as well as DBO rights being set - which I think is RIDICULOUS!)
I also dont want to go down the ADO route and insert each row as this will take HOURs as files are over 50,000 rows
September 27, 2004 at 8:49 am
Hey,
I don't think you can do that, because I thought select into created a new table, which would need dbo or ddladmin rights. Regardless select into would probably need insert rights because it is an insert action.
Can you assign that step to the sa account (or other SQL Server login), and get the dba's to do that? That doesn't seem to make much sense to do it that way.
Brian
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply