468,292 Members | 1,491 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,292 developers. It's quick & easy.

Bulkadmin role (BULK INSERT)

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!
Jul 20 '05 #1
2 15786
> 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!

Jul 20 '05 #2
"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

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by php newbie | last post: by
5 posts views Thread by me | last post: by
6 posts views Thread by pk | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
1 post views Thread by avicentic | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by Teichintx | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.