Sql 2000 permissions help needed!

• GregLyon

  SSCommitted

  Points: 1946

  More actions

  June 25, 2004 at 12:36 pm

  #161072

  I'm tangled up in permissions and could sure use a bit of help here.  I have an application and a database that work fine in SQL 7, but as I'm moving to SQL 2000 (on a server with much tighter security) things are breaking all over the place.

  the newest situation is as follows:

  1.  I have a front end application that sets up some #temp tables (I can test that they have in fact been created)
  2. Then a stored procedure runs to bulk insert some data to them.  The problem is that there is only one user that can do BULK INSERT in my database. 
  3. Other stored procedures perform data cleansing, some denormalization, and finally import the data to the live tables after some user interaction.

  So, I think that the BULK INSERT stored procedure somehow has to run as the bulkuser account, yet be able to access the temp tables that were created in the context of the user running the import process. 

  • So far both the user account AND the bulkuser account are members of db_owner. 
  • The stored procedures are all owned by dbo.  (Does that mean that they all run as dbo too?)
  • dbo does NOT have BULK INSERT priveledges.
  • The procedures run, but don't appear to import data.

  So...finally...the questions:

  1. Can a user run a stored procedure as my bulkuser account?
  2. Can the bulkuser account access #temp files created by the user?
  3. What special steps do I have to do to accomplish this.

  Thanks in advance!

  p.s. I found this link and have switched to a staging table in my db, but it still hasn't fixed the problem...BUG: Cannot Perform BULK INSERT with Bulkadmin Privileges.  I assume my bulkuser account was not granted db_owner or ddl_admin on TEMPDB...

• June 28, 2004 at 8:00 am

  This was removed by the editor as SPAM

• Linda Johanning

  SSCertifiable

  Points: 6727

  More actions

  June 28, 2004 at 11:17 am

  #512236

  If you are moving to a SQL 2000 box with 'much tighter security', why not make the security the same as the original box and begin 'tightening' one piece at a time?  It may be the result of something you have changed.

  Linda

• GregLyon

  SSCommitted

  Points: 1946

  More actions

  June 28, 2004 at 3:12 pm

  #512289

  Thanks for the response, Linda.  The 'much tighter security' is out of my control!  My database is being moved from a departmental server (mine) to a corporate server in which I have no control.  I'm a dbo of my database, but have no sysadmin priveledges. 

  The big issue is letting users BULK INSERT without being BULK ADMINS.  From various posts here and in the security forum I've given up on this approach.  What I'm doing now is creating a separate connection from my client app that uses the bulk_user account to do the bulk insert to a staging table.  THEN the data can be handled normally by my DB.

  Still, it confuses me.  I wish there were some documentation explaining why I can't do what I was trying to do.  Testing makes me believe that the BULK INSERT priveledge is not transferrable via Stored procedure in the way that typical GRANT permissions are, but I'd still like to see it in print somewhere!