-> Same server
-> Two databases (database A and database B)
-> 1 sql stored procedure in database A calls a stored procedure in database B and stores results in a temp table. Then first stored procedure does a select against the temp table to aggregate and sort the results.
-> It works via query analyzer (exec Stored Procedure in A which calls SP in B)
-> when I call it using VBA (Set rs = cmd.Execute); the recordset remains closed (Operation is not allowed when the object is closed) and I cannot process results .... almost as if nothing is being returned.
-> I can execute stored procedure in B directly from VBA so it seems it is an 'execution' permission problem but I've granted exec permissions to both stored procedure for the user I am using.