Bulk Insert for non admins

Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
instance of SQL on my desktop. I create an SQL Login named BITest and allow
him into my Pubs DB. I then add him to the BulkAdmin fixed server role. When
I have him try to Bulk Insert, I get:
Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of table
'pubs.dbo.t_access'. Cannot perform SET operation.
Which sort of indicates he needs to be in the db_owner role? Even though I
think thats crap, I add him to db_owner, and then try to run my Bulk Insert
again. This time I get:
Server: Msg 4834, Level 16, State 1, Line 1
You do not have permission to use the BULK INSERT statement.
So, not only does it really appear that he nees to be part of db_owner, it
still doesnt work. What gives?
--
TIA,
ChrisRChris,
From BOL:
Permissions
Only members of the sysadmin and bulkadmin fixed server roles can execute
BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
> instance of SQL on my desktop. I create an SQL Login named BITest and
> allow
> him into my Pubs DB. I then add him to the BulkAdmin fixed server role.
> When
> I have him try to Bulk Insert, I get:
> Server: Msg 8104, Level 16, State 2, Line 1
> The current user is not the database or object owner of table
> 'pubs.dbo.t_access'. Cannot perform SET operation.
> Which sort of indicates he needs to be in the db_owner role? Even though
> I
> think thats crap, I add him to db_owner, and then try to run my Bulk
> Insert
> again. This time I get:
> Server: Msg 4834, Level 16, State 1, Line 1
> You do not have permission to use the BULK INSERT statement.
> So, not only does it really appear that he nees to be part of db_owner, it
> still doesnt work. What gives?
> --
> TIA,
> ChrisR|||Jerry, as indicated in my post, I added him to that role.
--
TIA,
ChrisR
"Jerry Spivey" wrote:
> Chris,
> From BOL:
> Permissions
> Only members of the sysadmin and bulkadmin fixed server roles can execute
> BULK INSERT.
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
> > instance of SQL on my desktop. I create an SQL Login named BITest and
> > allow
> > him into my Pubs DB. I then add him to the BulkAdmin fixed server role.
> > When
> > I have him try to Bulk Insert, I get:
> >
> > Server: Msg 8104, Level 16, State 2, Line 1
> > The current user is not the database or object owner of table
> > 'pubs.dbo.t_access'. Cannot perform SET operation.
> >
> > Which sort of indicates he needs to be in the db_owner role? Even though
> > I
> > think thats crap, I add him to db_owner, and then try to run my Bulk
> > Insert
> > again. This time I get:
> >
> > Server: Msg 4834, Level 16, State 1, Line 1
> > You do not have permission to use the BULK INSERT statement.
> >
> > So, not only does it really appear that he nees to be part of db_owner, it
> > still doesnt work. What gives?
> >
> > --
> > TIA,
> > ChrisR
>
>|||Chris,
I got it to work with BULKADMIN and DB_OWNER. How many records to you have?
Do you really need to use BCP/BULK INSERT or would DTS possibly work?
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
> Jerry, as indicated in my post, I added him to that role.
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
>> Chris,
>> From BOL:
>> Permissions
>> Only members of the sysadmin and bulkadmin fixed server roles can execute
>> BULK INSERT.
>> HTH
>> Jerry
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
>> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a
>> > local
>> > instance of SQL on my desktop. I create an SQL Login named BITest and
>> > allow
>> > him into my Pubs DB. I then add him to the BulkAdmin fixed server
>> > role.
>> > When
>> > I have him try to Bulk Insert, I get:
>> >
>> > Server: Msg 8104, Level 16, State 2, Line 1
>> > The current user is not the database or object owner of table
>> > 'pubs.dbo.t_access'. Cannot perform SET operation.
>> >
>> > Which sort of indicates he needs to be in the db_owner role? Even
>> > though
>> > I
>> > think thats crap, I add him to db_owner, and then try to run my Bulk
>> > Insert
>> > again. This time I get:
>> >
>> > Server: Msg 4834, Level 16, State 1, Line 1
>> > You do not have permission to use the BULK INSERT statement.
>> >
>> > So, not only does it really appear that he nees to be part of db_owner,
>> > it
>> > still doesnt work. What gives?
>> >
>> > --
>> > TIA,
>> > ChrisR
>>|||I have 48 reconds. DTS may work, but I'd really like to try to get this
going. You're user is NOT a member of either SA of Admins, right?
--
TIA,
ChrisR
"Jerry Spivey" wrote:
> Chris,
> I got it to work with BULKADMIN and DB_OWNER. How many records to you have?
> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
> > Jerry, as indicated in my post, I added him to that role.
> > --
> > TIA,
> > ChrisR
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Chris,
> >>
> >> From BOL:
> >>
> >> Permissions
> >> Only members of the sysadmin and bulkadmin fixed server roles can execute
> >> BULK INSERT.
> >>
> >> HTH
> >>
> >> Jerry
> >>
> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> >> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> >> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a
> >> > local
> >> > instance of SQL on my desktop. I create an SQL Login named BITest and
> >> > allow
> >> > him into my Pubs DB. I then add him to the BulkAdmin fixed server
> >> > role.
> >> > When
> >> > I have him try to Bulk Insert, I get:
> >> >
> >> > Server: Msg 8104, Level 16, State 2, Line 1
> >> > The current user is not the database or object owner of table
> >> > 'pubs.dbo.t_access'. Cannot perform SET operation.
> >> >
> >> > Which sort of indicates he needs to be in the db_owner role? Even
> >> > though
> >> > I
> >> > think thats crap, I add him to db_owner, and then try to run my Bulk
> >> > Insert
> >> > again. This time I get:
> >> >
> >> > Server: Msg 4834, Level 16, State 1, Line 1
> >> > You do not have permission to use the BULK INSERT statement.
> >> >
> >> > So, not only does it really appear that he nees to be part of db_owner,
> >> > it
> >> > still doesnt work. What gives?
> >> >
> >> > --
> >> > TIA,
> >> > ChrisR
> >>
> >>
> >>
>
>|||I'd recommend DTS but it is your choice. No...created new login and user to
test for your example.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...
>I have 48 reconds. DTS may work, but I'd really like to try to get this
> going. You're user is NOT a member of either SA of Admins, right?
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
>> Chris,
>> I got it to work with BULKADMIN and DB_OWNER. How many records to you
>> have?
>> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
>> HTH
>> Jerry
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
>> > Jerry, as indicated in my post, I added him to that role.
>> > --
>> > TIA,
>> > ChrisR
>> >
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Chris,
>> >>
>> >> From BOL:
>> >>
>> >> Permissions
>> >> Only members of the sysadmin and bulkadmin fixed server roles can
>> >> execute
>> >> BULK INSERT.
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >>
>> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> >> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
>> >> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a
>> >> > local
>> >> > instance of SQL on my desktop. I create an SQL Login named BITest
>> >> > and
>> >> > allow
>> >> > him into my Pubs DB. I then add him to the BulkAdmin fixed server
>> >> > role.
>> >> > When
>> >> > I have him try to Bulk Insert, I get:
>> >> >
>> >> > Server: Msg 8104, Level 16, State 2, Line 1
>> >> > The current user is not the database or object owner of table
>> >> > 'pubs.dbo.t_access'. Cannot perform SET operation.
>> >> >
>> >> > Which sort of indicates he needs to be in the db_owner role? Even
>> >> > though
>> >> > I
>> >> > think thats crap, I add him to db_owner, and then try to run my Bulk
>> >> > Insert
>> >> > again. This time I get:
>> >> >
>> >> > Server: Msg 4834, Level 16, State 1, Line 1
>> >> > You do not have permission to use the BULK INSERT statement.
>> >> >
>> >> > So, not only does it really appear that he nees to be part of
>> >> > db_owner,
>> >> > it
>> >> > still doesnt work. What gives?
>> >> >
>> >> > --
>> >> > TIA,
>> >> > ChrisR
>> >>
>> >>
>> >>
>>|||Curious, why DTS? Also, Im running SQL2K, and you?
--
TIA,
ChrisR
"Jerry Spivey" wrote:
> I'd recommend DTS but it is your choice. No...created new login and user to
> test for your example.
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...
> >I have 48 reconds. DTS may work, but I'd really like to try to get this
> > going. You're user is NOT a member of either SA of Admins, right?
> > --
> > TIA,
> > ChrisR
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Chris,
> >>
> >> I got it to work with BULKADMIN and DB_OWNER. How many records to you
> >> have?
> >> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
> >>
> >> HTH
> >>
> >> Jerry
> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> >> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
> >> > Jerry, as indicated in my post, I added him to that role.
> >> > --
> >> > TIA,
> >> > ChrisR
> >> >
> >> >
> >> > "Jerry Spivey" wrote:
> >> >
> >> >> Chris,
> >> >>
> >> >> From BOL:
> >> >>
> >> >> Permissions
> >> >> Only members of the sysadmin and bulkadmin fixed server roles can
> >> >> execute
> >> >> BULK INSERT.
> >> >>
> >> >> HTH
> >> >>
> >> >> Jerry
> >> >>
> >> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> >> >> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> >> >> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a
> >> >> > local
> >> >> > instance of SQL on my desktop. I create an SQL Login named BITest
> >> >> > and
> >> >> > allow
> >> >> > him into my Pubs DB. I then add him to the BulkAdmin fixed server
> >> >> > role.
> >> >> > When
> >> >> > I have him try to Bulk Insert, I get:
> >> >> >
> >> >> > Server: Msg 8104, Level 16, State 2, Line 1
> >> >> > The current user is not the database or object owner of table
> >> >> > 'pubs.dbo.t_access'. Cannot perform SET operation.
> >> >> >
> >> >> > Which sort of indicates he needs to be in the db_owner role? Even
> >> >> > though
> >> >> > I
> >> >> > think thats crap, I add him to db_owner, and then try to run my Bulk
> >> >> > Insert
> >> >> > again. This time I get:
> >> >> >
> >> >> > Server: Msg 4834, Level 16, State 1, Line 1
> >> >> > You do not have permission to use the BULK INSERT statement.
> >> >> >
> >> >> > So, not only does it really appear that he nees to be part of
> >> >> > db_owner,
> >> >> > it
> >> >> > still doesnt work. What gives?
> >> >> >
> >> >> > --
> >> >> > TIA,
> >> >> > ChrisR
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Its fast, friendly (permissions issue in your case) and more functional.
SQL 2000 as well.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B4925F9E-6BBB-477E-B4B7-9817D10CB89B@.microsoft.com...
> Curious, why DTS? Also, Im running SQL2K, and you?
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
>> I'd recommend DTS but it is your choice. No...created new login and user
>> to
>> test for your example.
>> HTH
>> Jerry
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...
>> >I have 48 reconds. DTS may work, but I'd really like to try to get this
>> > going. You're user is NOT a member of either SA of Admins, right?
>> > --
>> > TIA,
>> > ChrisR
>> >
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Chris,
>> >>
>> >> I got it to work with BULKADMIN and DB_OWNER. How many records to you
>> >> have?
>> >> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> >> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
>> >> > Jerry, as indicated in my post, I added him to that role.
>> >> > --
>> >> > TIA,
>> >> > ChrisR
>> >> >
>> >> >
>> >> > "Jerry Spivey" wrote:
>> >> >
>> >> >> Chris,
>> >> >>
>> >> >> From BOL:
>> >> >>
>> >> >> Permissions
>> >> >> Only members of the sysadmin and bulkadmin fixed server roles can
>> >> >> execute
>> >> >> BULK INSERT.
>> >> >>
>> >> >> HTH
>> >> >>
>> >> >> Jerry
>> >> >>
>> >> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> >> >> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
>> >> >> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I
>> >> >> > have a
>> >> >> > local
>> >> >> > instance of SQL on my desktop. I create an SQL Login named BITest
>> >> >> > and
>> >> >> > allow
>> >> >> > him into my Pubs DB. I then add him to the BulkAdmin fixed
>> >> >> > server
>> >> >> > role.
>> >> >> > When
>> >> >> > I have him try to Bulk Insert, I get:
>> >> >> >
>> >> >> > Server: Msg 8104, Level 16, State 2, Line 1
>> >> >> > The current user is not the database or object owner of table
>> >> >> > 'pubs.dbo.t_access'. Cannot perform SET operation.
>> >> >> >
>> >> >> > Which sort of indicates he needs to be in the db_owner role?
>> >> >> > Even
>> >> >> > though
>> >> >> > I
>> >> >> > think thats crap, I add him to db_owner, and then try to run my
>> >> >> > Bulk
>> >> >> > Insert
>> >> >> > again. This time I get:
>> >> >> >
>> >> >> > Server: Msg 4834, Level 16, State 1, Line 1
>> >> >> > You do not have permission to use the BULK INSERT statement.
>> >> >> >
>> >> >> > So, not only does it really appear that he nees to be part of
>> >> >> > db_owner,
>> >> >> > it
>> >> >> > still doesnt work. What gives?
>> >> >> >
>> >> >> > --
>> >> >> > TIA,
>> >> >> > ChrisR
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>