BULK INSERT permissions for non-admin?

I've got a user, "joe", logging into a SqlServer 2005 database using
sqlserver authentication. Joe can create tables, insert, select, drop --
whatever -- but when he tries to do a bulk insert, he gets:
"The current user is not the database or object owner of table 'myTable'.
Cannot perform SET operation."
WARNING: I don't know much about SQL Server permissions administration. I
write queries. :)
When I look at the database in SQL Server Management Studio, I see that the
table is in the schema "dbo". What's the safest way to give Joe permission
to do a bulk insert into this table?
Thanks!"Jesse" <nospam@.nospam.com> wrote in message
news:OI6lteXZGHA.3848@.TK2MSFTNGP05.phx.gbl...
> I've got a user, "joe", logging into a SqlServer 2005 database using
> sqlserver authentication. Joe can create tables, insert, select, drop --
> whatever -- but when he tries to do a bulk insert, he gets:
> "The current user is not the database or object owner of table 'myTable'.
> Cannot perform SET operation."
> WARNING: I don't know much about SQL Server permissions administration. I
> write queries. :)
> When I look at the database in SQL Server Management Studio, I see that
> the table is in the schema "dbo". What's the safest way to give Joe
> permission to do a bulk insert into this table?
>
The user needs INSERT and ADMINISTER BULK OPERATIONS permissions and perhaps
ALTER TABLE. In addition if the user is connected using SQL Server
authentication then the SQL Server account must be able to read the file.
If the user is authenticated by Windows Integrated Authentication, then the
user, not the SQL Server account, needs to be able to read the file.
It's all in the BOL:
BULK INSERT (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188365(SQL.90).aspx
Security Considerations for Using Transact-SQL to Bulk Import Data
http://msdn2.microsoft.com/en-us/library/ms186286.aspx
David|||David --
Thanks for the quick reply! Unfortunately, I must still be missing
something -- I've gone through and granted everything I can think of, but I
still get the same error.
Specifically, here's what I did:
grant alter to [joe]
grant ADMINISTER BULK OPERATIONS to [joe]
grant insert to [joe]
Then, from Server Management Studio,
add the bulkadmin role to joe
add the symin role to joe
add the db_ddladmin role to joe
add the db_owner role to joe
Joe now has the power to destroy the whole database -- but not to bulk
insert. I still get, "The current user is not the database or object owner
of table 'myTable'. Cannot perform SET operation."
What else could I be missing?
Thanks again for your help!
Jesse
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OusnToXZGHA.428@.TK2MSFTNGP02.phx.gbl...
> "Jesse" <nospam@.nospam.com> wrote in message
> news:OI6lteXZGHA.3848@.TK2MSFTNGP05.phx.gbl...
>
> The user needs INSERT and ADMINISTER BULK OPERATIONS permissions and
> perhaps ALTER TABLE. In addition if the user is connected using SQL
> Server authentication then the SQL Server account must be able to read the
> file. If the user is authenticated by Windows Integrated Authentication,
> then the user, not the SQL Server account, needs to be able to read the
> file.
> It's all in the BOL:
>
> BULK INSERT (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188365(SQL.90).aspx
>
> Security Considerations for Using Transact-SQL to Bulk Import Data
> http://msdn2.microsoft.com/en-us/library/ms186286.aspx
>
>
> David
>
>|||One more detail:
- if I log into the database using Server Management Studio, I can run the
bulk insert OK.
- if I try to run it from an ASP.NET script, I get the permissions error.
The asp.net script is connecting to the database with the same credentials I
use to log in via SMS! Something must be working differently, but what?
This is driving me nuts. Thanks for any help you can give!
Here's how my asp.net script connects:
<add name="production" connectionString="data source=myLocalMachine; initial
catalog=myCatalog; user=joe; password=joe;"
providerName="System.Data.SqlClient"/>
"Jesse" <nospam@.nospam.com> wrote in message
news:e8jp30XZGHA.4884@.TK2MSFTNGP02.phx.gbl...
> David --
> Thanks for the quick reply! Unfortunately, I must still be missing
> something -- I've gone through and granted everything I can think of, but
> I still get the same error.
> Specifically, here's what I did:
> grant alter to [joe]
> grant ADMINISTER BULK OPERATIONS to [joe]
> grant insert to [joe]
> Then, from Server Management Studio,
> add the bulkadmin role to joe
> add the symin role to joe
> add the db_ddladmin role to joe
> add the db_owner role to joe
> Joe now has the power to destroy the whole database -- but not to bulk
> insert. I still get, "The current user is not the database or object owner
> of table 'myTable'. Cannot perform SET operation."
> What else could I be missing?
> Thanks again for your help!
>
> Jesse|||"Jesse" <nospam@.nospam.com> wrote in message
news:eiNZPSYZGHA.4884@.TK2MSFTNGP02.phx.gbl...
> One more detail:
> - if I log into the database using Server Management Studio, I can run the
> bulk insert OK.
> - if I try to run it from an ASP.NET script, I get the permissions error.
> The asp.net script is connecting to the database with the same credentials
> I use to log in via SMS! Something must be working differently, but what?
> This is driving me nuts. Thanks for any help you can give!
>
> Here's how my asp.net script connects:
> <add name="production" connectionString="data source=myLocalMachine;
> initial catalog=myCatalog; user=joe; password=joe;"
> providerName="System.Data.SqlClient"/>
>
Strange.
This works for me.
Run this as a symin. I have a database called test and xp_cmdshell
enabled.
You might run Profiler to see what's actually being sent from the web site.
David
--setup.sql--
use test
master..xp_cmdshell 'dir > c:\data\foo.txt'
master..xp_cmdshell 'type c:\data\foo.txt'
create table LoadTable(data varchar(4000))
bulk insert LoadTable from 'c:\data\foo.txt'
select * from LoadTable
truncate table LoadTable
go
use master
create login Joe with password='Joe'
grant administer bulk operations to Joe
go
use test
create user Joe for login Joe
grant view definition on schema::dbo to Joe
grant select,insert,delete on LoadTable to Joe
--end setup.sql--
then run this .NET program
--Program.cs--
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Data.SqlClient;
namespace csTest
{
public static class Program
{
public static void Main()
{
SqlConnectionStringBuilder cb1 = new SqlConnectionStringBuilder();
cb1.DataSource = "192.168.2.10";
cb1.IntegratedSecurity = false;
cb1.UserID = "Joe";
cb1.Password = "Joe";
cb1.InitialCatalog = "Test";
using (SqlConnection con = new SqlConnection(cb1.ConnectionString))
{
con.Open();
con.FireInfoMessageEventOnUserErrors = true;
con.InfoMessage += new SqlInfoMessageEventHandler(con_InfoMessa
ge);
new SqlCommand(@."delete from LoadTable", con).ExecuteNonQuery();
new SqlCommand(@."bulk insert LoadTable from
'c:\data\foo.txt'",con).ExecuteNonQuery();
using (SqlDataReader rdr = new SqlCommand("select * from
LoadTable",con).ExecuteReader())
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
Console.ReadLine();
}
static void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine(e.Message);
}
}
}
--end Program.cs---