Re: Database roles

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

Date: Fri, 16 Sep 2005 13:04:01 -0400

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
>> >
>> >
>>
>>
>
>

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)
  • Database roles
    ... In SQL 2000 if a sql account has bulkadmin, datareader and datawriter it ... that enough to use bulk insert to a dbo table? ... I wanted to avoid giving the sql account dbo privileges. ...
    (microsoft.public.sqlserver.security)
  • Re: Database roles
    ... The code looks something like this - (if I give the sql account db_ddladmin ... then this executes without errors) ... This stored procedure does a bulk insert into CUSTODIAN_HOLDINGS_IMPORT ... > You mention a dbo table but the error suggests it is not dbo. ...
    (microsoft.public.sqlserver.security)
  • Re: Database roles
    ... You mention a dbo table but the error suggests it is not dbo. ... can you post the actual code you are trying to execute? ... "Meenal Dhody" wrote in message ... > I wanted to avoid giving the sql account dbo privileges. ...
    (microsoft.public.sqlserver.security)
  • Changing DBO
    ... >I have assigned an SQL account as DBO of a database. ... >am going to change the DBO to a Windows login account. ... >change the owner of the objects created by the SQL ...
    (microsoft.public.sqlserver.security)
Quantcast