Re: Database roles

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

Date: Fri, 16 Sep 2005 12:01:12 -0400

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
    ... > 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
    ... 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)
  • 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)
  • Form using stored procedure does not work in Access Runtime
    ... I am using a parameterised stored procedure as a recordsource of a form. ... In both situations for the same SQL account. ... WHERE CategoryID = @CategoryID ...
    (microsoft.public.access.adp.sqlserver)
  • 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)