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

Database Replication

I follow the considerations definied by document, but I have an error
when I insert data into a table, because it has an identity field, and
the replication return the following error:
An explicit value for the identity column in table 'SAR_Callejero' can
only be specified when a column list is used and IDENTITY_INSERT is
ON.
Then, I modified the insert sp and added the following:
SET IDENTITY_INSERT sar_callejero ON
But newly gave the same error.
Can you help me?
Thanks
Is your insert supplying an identity value when it shouldn't?
"cbaffigi@.apsf.com.ar" wrote:

> I follow the considerations definied by document, but I have an error
> when I insert data into a table, because it has an identity field, and
> the replication return the following error:
> An explicit value for the identity column in table 'SAR_Callejero' can
> only be specified when a column list is used and IDENTITY_INSERT is
> ON.
> Then, I modified the insert sp and added the following:
> SET IDENTITY_INSERT sar_callejero ON
> But newly gave the same error.
> Can you help me?
> Thanks
>

Database Problem

hello all

i am develope a travel website, in this site there is an 14 database tables

in my local machine all the work is done properly like (insert, update, delete, adminlogin , client login, etc...)

but in the internet or main server it not work properly

what is this prob.

plz help me

ashwani

what happens when you run it on the internet?

Does it give you an error Message or just not save changes to the database?

|||

it could be a connection string problem, the application cannot connect to the database, or ASP.NET account doesn't have the proper rights.

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroup
db_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en...urity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj> ). See this for other predefined roles:
http://msdn.microsoft.com/library/e...curity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup