Bulkadmin role (BULK INSERT)

[php newbie]

Hello,

I am trying to load a simple tab-delimited data file to SQL Server. I
created a format file to go with it, since the data file differs from
the destination table in number of columns.

When I execute the query, I get an error saying that only sysadmin or
bulkadmin roles are allowed to use the BULK INSERT statement. So, I
proceeded with the Enterprise Manager to grant myself those roles.
However, I could not find sysadmin or bulkadmin roles using the
Enterprise Manager. From what I read from my books, I thought these
were fixed server roles and that they would be there.

So I have a few questions:
1) How do I create a user account/role that can issue BULK INSERT
commands?

2) Why is BULK INSERT considered a dangerous operation that it
requires special privileges? What are its implications? I have a
couple of books that say that a user should be aware of its
implications before using it, but they don't actually describe what
those implications might be.

3) It seems that I can load the data file using BCP utility, without
such privileges. If so, what is the difference?

Thanks!

[Dan Guzman]

> So, I proceeded with the Enterprise Manager to grant myself those

roles. However, I could not find sysadmin or bulkadmin roles using the
Enterprise Manager. From what I read from my books, I thought these
were fixed server roles and that they would be there.

So I have a few questions:
1) How do I create a user account/role that can issue BULK INSERT
commands?
The roles are there but you need to be a sysadmin role member or a member of
that fixed server role in order to add members. Ask your DBA to do this.
2) Why is BULK INSERT considered a dangerous operation that it
requires special privileges? What are its implications? I have a
couple of books that say that a user should be aware of its
implications before using it, but they don't actually describe what
those implications might be.
The main security implication is that BULK INSERT accesses external data
under the security context of the SQL Server service account rather than the
invoking user's account.
3) It seems that I can load the data file using BCP utility, without
such privileges. If so, what is the difference?
Client-based bulk insert techniques like SQLOLEDB IRowsetFastLoad and ODBC
BCP access data under the security context of the invoking user.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"php newbie" <ne**********@yahoo.com> wrote in message
news:12**************************@posting.google.c om... Hello,

I am trying to load a simple tab-delimited data file to SQL Server. I
created a format file to go with it, since the data file differs from
the destination table in number of columns.

When I execute the query, I get an error saying that only sysadmin or
bulkadmin roles are allowed to use the BULK INSERT statement. So, I
proceeded with the Enterprise Manager to grant myself those roles.
However, I could not find sysadmin or bulkadmin roles using the
Enterprise Manager. From what I read from my books, I thought these
were fixed server roles and that they would be there.

So I have a few questions:
1) How do I create a user account/role that can issue BULK INSERT
commands?

2) Why is BULK INSERT considered a dangerous operation that it
requires special privileges? What are its implications? I have a
couple of books that say that a user should be aware of its
implications before using it, but they don't actually describe what
those implications might be.

3) It seems that I can load the data file using BCP utility, without
such privileges. If so, what is the difference?

Thanks!

[php newbie]

"Dan Guzman" <da*******@nospam-earthlink.net> wrote in message news:<bR*****************@newsread2.news.pas.earth link.net>...

So, I proceeded with the Enterprise Manager to grant myself those
roles. However, I could not find sysadmin or bulkadmin roles using the
Enterprise Manager. From what I read from my books, I thought these
were fixed server roles and that they would be there.

So I have a few questions:
1) How do I create a user account/role that can issue BULK INSERT
commands?
The roles are there but you need to be a sysadmin role member or a member of
that fixed server role in order to add members. Ask your DBA to do this.

Hello Dan,

This was for personal use, so that makes me the DBA. I believe I
disabled the "sa" account when I first installed SQL Server (based on
some suggestions due to security risks). Perhaps that has something
to do with it. I will look into it.

Client-based bulk insert techniques like SQLOLEDB IRowsetFastLoad and ODBC
BCP access data under the security context of the invoking user.
Thanks! This clarifies the risk implications of BULK INSERT vs. bcp
that was not in the books. It looks like Bcp is the sure way to go
for most users.

--
Hope this helps.

Dan Guzman
SQL Server MVP