Database roles

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
MeenalYou 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
>|||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...
following[vbcol=seagreen]
SET[vbcol=seagreen]
>|||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...
> event
> following
> SET
>|||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...
/ ****************************************
***********************************[vbco
l=seagreen]
****************************************
************************************[vbc
ol=seagreen]
update[vbcol=seagreen]
+[vbcol=seagreen]
perform[vbcol=seagreen]
>|||It seems as if this is a BUG.
http://support.microsoft.com/defaul...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...
> / ****************************************
*********************************
**
> ****************************************
**********************************
**
> update
> +
> perform
>|||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/defaul...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...
insert[vbcol=seagreen]
hard[vbcol=seagreen]
/ ****************************************
***********************************[vbco
l=seagreen]
****************************************
************************************[vbc
ol=seagreen]
failed'[vbcol=seagreen]
any[vbcol=seagreen]
>