By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,417 Members | 1,825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,417 IT Pros & Developers. It's quick & easy.

Bulkadmin role (BULK INSERT)

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
> 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

P: n/a
"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.