So, we're trying to get UPS WorldShip to work with SQL Server 2005. So here's how it looks from the user's perspective:
Put in your order number (WorldShip pulls the shipping information)
Put in the package weight (Worldship figures out the cost)
Hit OK to confirm everything and then wait 45 seconds'
Get a tracking number
Now, our shipping department sends out 100 packages a day. That little 45 second wait thing...that's an hour and a half of someone's time just waiting. So, what's going on there.
So I did a sql profiler on what was going on.
For some reason WorldShip is pulling a full copy of my entire database schema (calls sp_tables with the "table" parameter, then calls sp_columns for EVERY TABLE IN THE DATABASE) every time someone wants to ship.
This is..um...1700 tables. Somewhat ludicrous.
So, there are two solutions for this. Since the security situation on that server is a complete mess (vendor requires it to be a complete mess) I chose to create another database, put a view in that database that pointed to the original table in the original database. The application administrator then reconfigured WorldShip to work with the new view, and everything's great now. Views: Is there anything they can't do?
The better solution would be to create a WorldShip user and only give them access to one table. That would *probably* work, but I'm not sure it'd work, and I'm not sure sp_tables respects all of the same conventions as a select * from sysobjects does as far as user security goes.