Re: Database roles

From: Meenal Dhody (meenal_dhody_at_hotmail.com)
Date: 09/16/05

  • Next message: Andrew J. Kelly: "Re: Database roles" 
    Date: Fri, 16 Sep 2005 14:51:12 -0400

    I tried a simpler statement like this:

    BULK INSERT gds.dbo.tmpAccountAddress FROM 'd:\data\nalist.dat'

    and got the following message:

    The current user is not the database or object owner of table
    'gds.dbo.tmpAccountAddress'. Cannot perform SET operation.

    Is the only option to maybe create the table that you need to bulk insert to
    under that login?

    thanks

    Meenal
    "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
    news:eCqBnCuuFHA.2064@TK2MSFTNGP09.phx.gbl...
    > Maybe it has to do with the fact you are using EXEC as well. If you hard
    > code the filename and format file will that run?
    >
    > --
    > Andrew J. Kelly SQL MVP
    >
    >
    > "Meenal Dhody" <meenal_dhody@hotmail.com> wrote in message
    > news:uLb0JgtuFHA.3596@TK2MSFTNGP15.phx.gbl...
    > > The code looks something like this - (if I give the sql account
    > > db_ddladmin
    > > then this executes without errors)
    > > CREATE PROCEDURE [dbo].[sp_Custodian_ImportHoldings]
    > > @BatchId VARCHAR(25),
    > > @FilePath VARCHAR(200),
    > > @FormatFile VARCHAR(200),
    > > @CustodianId INT
    > > AS
    > >
    /***************************************************************************
    > > ****
    > > Procedure: sp_Custodian_ImportHoldings
    > > This stored procedure does a bulk insert into CUSTODIAN_HOLDINGS_IMPORT
    > >
    ****************************************************************************
    > > ***/
    > >
    > > DECLARE @statusMsg VARCHAR(255)
    > > SET NOCOUNT ON
    > >
    > > -- Delete from CUSTODIAN_HOLDINGS_IMPORT for the Custodian Source before
    > > doing a bulk insert
    > > DELETE FROM GDS.DBO.HOLDINGS_IMPORT WHERE CUSTODIANID=@CustodianId
    > >
    > > SET @statusMsg = 'Loading CUSTODIAN_HOLDINGS_IMPORT'
    > >
    > > -- Transaction to do Bulk insert into CUSTODIAN_HOLDINGS_IMPORT and
    update
    > > CUSTODIANID,GDSSOURCEID
    > > BEGIN TRANSACTION trans_BulkInsert_Update
    > >
    > > -- Bulk insert into the CUSTODIAN_HOLDINGS_IMPORT table
    > > EXEC ('BULK INSERT GDS.DBO.HOLDINGS_IMPORT FROM ''' + @FilePath + ''''
    +
    > > ' WITH (FORMATFILE = '''+ @FormatFile + ''')')
    > >
    > > IF ( @@ERROR <> 0 )
    > > BEGIN
    > > SET @statusMsg = 'CUSTODIAN_HOLDINGS_IMPORT - Bulk insert failed'
    > > PRINT @statusMsg
    > > ROLLBACK
    > > RAISERROR('HOLDINGS_IMPORT_FAILURE', 16, 1)
    > > RETURN -1
    > > END
    > >
    > >
    > >
    > > thanks
    > > Meenal
    > >
    > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
    > > news:%23V5q3csuFHA.4020@TK2MSFTNGP12.phx.gbl...
    > >> You mention a dbo table but the error suggests it is not dbo. In any
    > > event
    > >> can you post the actual code you are trying to execute?
    > >>
    > >> --
    > >> Andrew J. Kelly SQL MVP
    > >>
    > >>
    > >> "Meenal Dhody" <meenal_dhody@hotmail.com> wrote in message
    > >> news:u7laRHnuFHA.3256@TK2MSFTNGP09.phx.gbl...
    > >> > Hi,
    > >> >
    > >> > In SQL 2000 if a sql account has bulkadmin, datareader and datawriter
    > >> > it
    > >> > that enough to use bulk insert to a dbo table? I am getting the
    > > following
    > >> > error with the current permissions:
    > >> >
    > >> > Currently user is not the owner of GDS.HOLDINGS_IMPORT. Cannot
    perform
    > > SET
    > >> > operation.
    > >> >
    > >> > I wanted to avoid giving the sql account dbo privileges.
    > >> >
    > >> > thanks
    > >> > Meenal
    > >> >
    > >> >
    > >>
    > >>
    > >
    > >
    >
    >

  • Next message: Andrew J. Kelly: "Re: Database roles"

    Relevant Pages

    • Re: Database roles
      ... Andrew J. Kelly SQL MVP ... > Is the only option to maybe create the table that you need to bulk insert ... >> Andrew J. Kelly SQL MVP ... >>>> You mention a dbo table but the error suggests it is not dbo. ...
      (microsoft.public.sqlserver.security)
    • Re: [PATCH 0/5] WorkStruct: Shrink work_struct by two thirds
      ... to be not too painful for Andrew. ... _or_ after Andrew has merged the bulk of his stuff, ... Most of the outstanding work is in git trees, ...
      (Linux-Kernel)
    • Re: [PATCH 0/5] WorkStruct: Shrink work_struct by two thirds
      ... Linus Torvalds wrote: ... to be not too painful for Andrew. ... _or_ after Andrew has merged the bulk of his stuff, ...
      (Linux-Kernel)
    • Re: copying large SQL SERVER tables between DBs
      ... Take a look at BCP and BULK INSERT in BooksOnLine. ... Andrew J. Kelly SQL MVP ... "Avi Perez" wrote in message ... > a DB and an external data file. ...
      (microsoft.public.sqlserver.server)
    • Bulk Insert Error
      ... I could not able to bulk insert into a temporary table. ... The current user is not the database or object owner of table '#TempTable'. ... Regards, ...
      (microsoft.public.sqlserver.security)