I have an application which uses the sp_approle() to control access to the database only via the application. Unfortunately I also have the need to get it to import XML files via the XMLbulkload object and this is not working.
Aparantly, the xmlbulkload is trying to make another connection to the database and its not using the sp_approle() - obviously, so the database kicks me off saying I do not have permissions to run a SELECT query on a temporary table.
I look at the XMLBulkLoad object and it has a property called ConnectionCommand, where one can pass an EXISTING connection to the XMLBulkLoad. I am pretty confident this will work, but the documentation does not say whether the object, for that property has to be a CONNECTION or a COMMAND ADO object.
My existing connection uses an ADODB.Connection object. Am I forced to change this to a command object? It will drastically alter the code in my application!
This is just a thought, but you could brew up a login that was used solely for the XMLBulkLoad, and only have the application know its password (maybe even use a password that can't be typed, only generated as a string using code).