Statements work, Stored procedure doesnt...

Question

Statements work, Stored procedure doesnt...

GregLyon

SSCommitted

Points: 1946
More actions
June 24, 2004 at 4:38 pm

#108412

I'm pulling my hair out on this one...
I've written a stored procedure that BULK INSERTs some data to a staging table, then moves it to the production table if it passes some tests. This worked fine in SQL 7, but has been somewhat rewritten in SQL 2000. Here is the code:
ALTER PROCEDURE dbo.pr_ProdImportAldb
    (
      @ImportFileSpec VarChar(255)
    , @FormatFileSpec VarChar(255)
    , @OutputTimes Bit
    )
As
    DECLARE
      @DateString VarChar(7)
    , @DateVal DateTime
    , @RowCount Int

    --Truncate table...
    TRUNCATE TABLE tmpBIALDB
    --Perform Bulk Insert...
    EXEC ('BULK INSERT tmpBIALDB FROM ''' + @ImportFileSpec + ''' WITH ( FORMATFILE = ''' + @FormatFileSpec + ''')')
    --Check to see if this month has already been added
    SET @DateString = (SELECT TOP 1 Summary_date FROM tmpBIALDB)
<snip>

The SET @DateString line bombs with the error message:
Invalid object name 'tmpBIALDB', Right after it BULK INSERTED to the exact same table...

BUT It only bombs if I try to run it as a stored procedure. If I run all these statements in sql query analyzer it runs fine.
So, what's up? Can you see the problem?
Notes: SQL 2000, running as a user with BULK ADMIN rights and DBO rights to my database, but no SYSADMIN rights.
Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GregLyon SSCommitted Points: 1946 More actions · Answer 1

Well...I solved the problem but now I need help to understand WHY it didn't work and WHY it does work now...

The solution was to drop and recreate the tmpBIALDB table. upon recreation I created it as dbo.tmpBIALDB instead of bulkuser.tmpBIALDB.

What I don't get, is that only bulkuser was ever running the statements or the stored procedure. So if it were gonna bomb wouldn't it be at the first line that refered to the tmpBIALDB table? Why did it run fine until the SET line. It appears to me that the SET line is somehow run in different security context, and that other context can't "see" the table...

Am I getting warm at all on this??? Please enlighten me!

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 2

BULK INSERT runs under the security context of SQL Server.

bulkuser is the owner of your table, and dbo is the owner of your sproc. Two different security accounts. If bulkuser owned both the table and the sproc, then you would find Ownership Chaining would have worked fine (like how it does now that you created the table as dbo.tmpBIALDB). Basically, you've just run into a permissions issue.

Checkout 'Using Ownership Chaining' http://msdn.microsoft.com/library/en-us/adminsql/ad_security_4iyb.asp

Julian Kuiters
juliankuiters.id.au

GregLyon SSCommitted Points: 1946 More actions · Answer 3

Thanks Julian, that's a good link! I realize I have a lot to learn about SQL Server 2000 permissions.

Couple more questions:

1) What I still don't understand is that my bulkuser account IS a member of the db_owner role. Is that different from being the specific dbo user in this context? I assumed that would be enough permissions for it to work, but obviously not.

I'm also not clear about the relationships between the user running an application vs the stored procedures that get called vs the tables, views that get referenced (and temp tables). The Link you gave hints at it, but can you point me to a book or more involved reference???

Thanks in advance!

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 4

1) dbo is a user, while db_owner is a role. There are subtle but important differences.

To learn more about permissioning and ownership in SQL Server, I'd recommend reading through all the security, permissions, users and roles pages in SQL BOL.

Heres a snippet from SQL BOL

Database Owner (dbo)

The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.

For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1.

The dbo user cannot be deleted and is always present in every database.

Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):

Belong to the user creating the object, not dbo.
Are qualified with the name of the user who created the object.

Julian Kuiters
juliankuiters.id.au