Re: Database roles

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 09/17/05

  • Next message: Dan Guzman: "Re: Adds the group to the database But Security EM" 
    Date: Sat, 17 Sep 2005 16:15:14 -0400

    It seems as if this is a BUG.
    http://support.microsoft.com/default.aspx?scid=kb;en-us;302621

    You can add the ddl_admin like it states or try using BCP through
    xp_cmdshell. I don't believe you will have the same issue with bcp. 

    -- 
Andrew J. Kelly  SQL MVP
"Meenal Dhody" <meenal_dhody@hotmail.com> wrote in message 
news:u8mOJ$uuFHA.3500@TK2MSFTNGP09.phx.gbl...
>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: Dan Guzman: "Re: Adds the group to the database But Security EM"

    Relevant Pages

    • Re: Database roles
      ... > This stored procedure does a bulk insert into CUSTODIAN_HOLDINGS_IMPORT ... >> You mention a dbo table but the error suggests it is not dbo. ... >> Andrew J. Kelly SQL MVP ... >>> In SQL 2000 if a sql account has bulkadmin, ...
      (microsoft.public.sqlserver.security)
    • Re: Database roles
      ... The current user is not the database or object owner of table ... Is the only option to maybe create the table that you need to bulk insert to ... > Andrew J. Kelly SQL MVP ...
      (microsoft.public.sqlserver.security)
    • 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)
    • Re: Microsoft SQL Server 2000 BULK INSERT Buffer Overflow (#NISR11072002)
      ... > privileges of the database owner or dbo. ... In fact, you need to be a member of the sysadmin and bulkadmin fixed server roles to be able to execute BULK INSERT, both of these have to be explicitly set, if you're not user 'sa' ...
      (NT-Bugtraq)
    • RE: Microsoft SQL Server 2000 BULK INSERT Buffer Overflow (#NISR11072002)
      ... > privileges of the database owner or dbo. ... In fact, you need to be a member of the sysadmin and bulkadmin fixed server roles to be able to execute BULK INSERT, both of these have to be explicitly set, if you're not user 'sa' ...
      (Bugtraq)