none
error running SqlBulkCopy.WriteToServer(DataRow[] rows): "Cannot find the object TABLE_XXX" RRS feed

  • Question

  • I'm using .NET 4.0 version of SqlBulkCopy.WriteToServer(DataRow[] rows) and if my user has the database level role of db_owner everything works great.  

    We are trying to lock down our user to only the privs that it requires and we currently have these roles setup:

    • Database Level: db_datareader
    • Database Level: db_datawriter
    • Database Level: EXUCUTE on all stored procs, functions
    • Server level: bulkadmin

    With this current privilege setup the call to SqlBulkCopy.WriteToServer() is failing saying that it cannot find the table that it's supposed to be inserting into.  

    I'm sure the fix is just to add a priv or two but I can't seem to find that information.  Again, this user already has the bulkadmin server role and the code is still failing.

     

    Thank you in advance for the help!

    Tuesday, December 13, 2011 5:10 PM

Answers

  • I finally got it figured out, the user needed explicit GRANT ALTER on the table that I was inserting into.

    GRANT ALTER ON [dbo].[TABLE_XXX] TO [appuser] 

     

    Thanks everyone for the help!

    • Marked as answer by Jhilden Thursday, December 15, 2011 3:17 PM
    Thursday, December 15, 2011 3:17 PM

All replies

  • Or the table name is mispelled, or the code is poking in the wrong database.

    If you truly think it is a permissions issue, but the user you are testing with in sysadmin and see if that changes things. I'm not suggesting this is a solution, just as a way of troubleshooting.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 13, 2011 10:43 PM
  • Erland, thank you for the reply.  What you suggested is exactly how I tested it.  Without changing any code if I just make the user a sysadmin everything works fine.  This is how the user is currently setup in production, that's what I'm trying to fix.
    Wednesday, December 14, 2011 2:49 PM
  • Erland, thank you for the reply.  What you suggested is exactly how I tested it.  Without changing any code if I just make the user a sysadmin everything works fine.  This is how the user is currently setup in production, that's what I'm trying to fix.

    OK. I just wanted to make sure. I can't see anything that is missing.

    What happens if you in SQL Server Management Studio runs:

    EXECUTE AS LOGIN = 'this_usr'
    go
    SELECT * FROM TABLE_XXX
    go
    REVERT

    One possibility is that .Net runs some commands behind the scenes that
    need more permissions. Use Profiler to see if there are any such commands.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 14, 2011 11:14 PM
  • Grant INSERT and SELECT on that table for that user (assuming it is finding it and permissions are the issue)
    Wednesday, December 14, 2011 11:31 PM
  • I finally got it figured out, the user needed explicit GRANT ALTER on the table that I was inserting into.

    GRANT ALTER ON [dbo].[TABLE_XXX] TO [appuser] 

     

    Thanks everyone for the help!

    • Marked as answer by Jhilden Thursday, December 15, 2011 3:17 PM
    Thursday, December 15, 2011 3:17 PM
  • One reason that we may need the ALTER privilege is that we are using BulkInsert with the option to KeepIdentity, this probably means that behind the scenes it is altering the table to turn off identity insert and then turn it back on.  At least that's my best guess.

     

    using (var bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))

    Thursday, December 15, 2011 3:23 PM