Re: Database roles

• Previous message: Sue Hoegemeier: "Re: deny a computer from connecting using sql authentication"
• In reply to: Andrew J. Kelly: "Re: Database roles"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Tue, 20 Sep 2005 00:01:05 -0400

Thanks - I went with creating the tables under the sql login so I would not
have to give the login ddladmin rights.

Meenal
"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:eecpIS8uFHA.2568@TK2MSFTNGP15.phx.gbl...
> 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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

• Previous message: Sue Hoegemeier: "Re: deny a computer from connecting using sql authentication"
• In reply to: Andrew J. Kelly: "Re: Database roles"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]