I have a piece of VB code, which is trying to import an XML file into temporary tables, in SQL server 2000.

My DBA has removed user group access to the database, so we are forced to use the sp_setapprole procedure to give access via the application and not via a user group, which would mean people could Access/Excel into the database and change it!

After opening up a CONNECTION to the SQL server database, I use this ...

db.Execute "sp_setapprole 'role_visdocs_ebs_int','#####'"

The '####' is where our password would go.

Furtherdown, I would pass the db connection to XMLbulkload so as to keep the approle intact for when XMLbulkload attempts to SELECT data from the XML files.

ie:

'build a command object for XMLbulkload later
Set Cmd = New ADODB.Command
Set Cmd.ActiveConnection = db '<-- 'db' is an existing OPEN connection
..
..
'load BulkLoad into memory
Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")

'set the path to where the XML files are stored
Set oXMLSourceFolder = oFScriptObj.GetFolder(gEBS_XMLPath)

'use current path's XSD schema file (if found), else error
wsXSDSchema = App.Path & "\EBS schema.xsd"
..
..
With oXMLBulkLoad
.ConnectionCommand = Cmd
.ErrorLogFile = gEBS_errLogFile
.CheckConstraints = True
.SGUseID = True
.Transaction = True
End With
..
..
'INSERT all XML file(s) found into SQLsrv
For Each oXMLFile In oXMLSourceFolder.Files

'make sure only XML files are processed
If Not basFS_GetFileExt(UCase$(Trim$(oXMLFile.Name))) = "XML" Then
GoTo SkipFile_ '<-- BAD programming!!!
End If

wsFile = gEBS_XMLPath & "\" & (oXMLFile.Name)

'################################################# ##########################################
'run the bulk load to import the XML
oXMLBulkLoad.Execute wsXSDSchema, wsFile
'################################################# ##########################################


The oXMLBulkLoad object fails at this point with the following error ...

"The current user is not the database or object owner of table 'dbo.tblOrderTemp'. Cannot perform SET operation. VB: -2147217900"

My DBA is also at a loss, because according to the trace he did, XMLBulkLoad correctly takes the existing DB connection and uses the existing setapprole(), but it seems to say that the bulkload is trying to gain access to INSERT the data into the temporary table, yet it has the privs to do so, as it can SELECT, but not INSERT!

Is there a security setting we have to do, to give the temporary tables WRITE access to XMLBulkLoad?

The temporary tables are really not temporary, as they have been created manually before-hand. We cannot allow XMLbulkload to create the temporary tables at run time because the constraints in the tables can only be created manually and they do have some complexity which we were not able to "program" in the XSD schema file.

Any ideas would be appreciated. Thanks