BULK INSERT / TRUNCATE permission problems

Question

BULK INSERT / TRUNCATE permission problems

Richard_Hadfield

Ten Centuries

Points: 1113
More actions
June 30, 2004 at 3:03 am

#152208

This is more of a security question, however based on the audiance...
I am trying to come up with a security strategy for running stored procedures that might include bulk insert and truncate statements.
The problem is the users only have execute permissions on the stored procedures and there are customised views they use to interrogate information / run reports.
Other issues I have run into are changing views on the fly with dynamic sql which too runs into the whole dbo problem...
Currently I have a vb .exe that logs in as me sysadmin, which I am not too comfortable with, is there a "smarter" way of doing this. For example I can use the sp_changedbowner temporarily then change it back somehow...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 1

Richard,

I believe that, with SQL 2000, there is a BULK INSERT role where you no longer have to grant a user "SA" rights just so they can use the BULK INSERT command.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Richard_Hadfield Ten Centuries Points: 1113 More actions · Answer 2

BOL states that Only members of the sysadmin and bulkadmin fixed server roles can execute BULK INSERT.

So I have set users to be members of this role however, it only seems to work if they are dbo as well...

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 3

Of course, Microsoft documentation is always 100% correct

What you may want to try, instead, is calling BCP using xp_shell... that is unless you have heightened security where xp_shell has been disabled (good idea, actually). In that case, you could set up BCP to run from a DOS Batch File which may also be scheduled to run using Windows Task Scheduler.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Richard_Hadfield Ten Centuries Points: 1113 More actions · Answer 4

Thanks Jeff at least it gives me a new angle, as far as I am aware xp_shell is enabled.

Wayne-153714 Hall of Fame Points: 3536 More actions · Answer 5

Be wary using xp_shell. You need sa rights to execute xp_shell, but remember that it can be used in other ways than BCP. Somone with access to execute xp_shell on your server can do things like deleting system files and databases, backups. If the SQL Server login account is a domain administrator (if it is, think about changing it) then the user can use xp_shell to access any resource over the network.

If I need to pump data into SQL, I would rather use BULK INSERT and grant the user bulkadmin role. To pump data out of SQL Server, we use parametized DTS packages.

When in doubt - test, test, test!

Wayne

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 6

Yeah, Wayne has confirmed what I've heard... you can do some really nasty things through xp_shell. Maybe you shouldn't go there. Just create the BCP job in a batch file and schedule it to run through the Windows Task Scheduler.

One other thing... I don't believe you need to give the SA role to the user for the whole database to do Bulk Inserts or Truncates... I think you can limit the roles for the user by table and, in some cases, the column of a table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Wayne-153714 Hall of Fame Points: 3536 More actions · Answer 7

Jeff is correct in his last point. If you just give the user the bulkadmin server role, and access to the table in question, the user can run a bulk insert on that table. As for truncate, try just giving delete access on the table to the user and see if that works.

When in doubt - test, test, test!

Wayne