Still cache refresh problem in final release

ccote

HI, I logged an inquiry in the beta forum last month telling that SQLPrompt 3 had a problem when connection change occurs. The problem is that the cache is not refreshed properly.

My SQL Server users have a default schema and I deny specifically view definition on other schema in the same database:
UserA and UserB has the following database roles:
db_ddladmin, db_datareader, db_datawriter.

UserA has default schemaA and UserB has default schemaB
Howver, UserB has a deny view definition on SchemaA objects and UserA as also a deny view definition on SchemaB objects.

The first time I connect to SQL Server, the cache is refreshed correctly and I can see the right objects in the candidate list. However, when I connect to another user (from UserA to UserB), the candidate list is not refreshed accurately. I still see SchemaA objects while I should see UserB objects (and visa-versa if I connect to UserA from UserB). The only way to see accurate schema object for a specific connection is to refresh the cache manually. The everything is fine.

IS there a way to automate the cache refreshing when I change user? I see the the cache is refreshed but not as much as when I call a full refresh. It looks like thet it checks if I connect to another DB and does n ot seems to refresh anything. But, the cache should be refreshed according to the current user's credentials no?

Thank you,
Ccote

Bart Read

Hi there,


Yes I'm sorry we were unable to fix every issue that was rasied against the beta. To be honest I'm really surprised this works at all since SQL Compare usually complains if you don't have dbo access to the database, and we use that to retrieve and store meta-data.

The reason we didn't fix it for the final release is basically that: SQL Compare doesn't normally work without dbo access, so in many cases the extra logic required in the cache control layer would have been wasted and would have potentially reduced other bugs. To actually sort this out properly would require quite a few weeks of effort and some fairly significant changes to SQL Compare which would be very risky. We could obviously branch the codebase but that is also not without its problems.

So all I can say for now is that I'm sorry we were unable to fix it, but obviously we're going to be looking at ways to get the meta-data model to work better for SQL Prompt. Hopefully that at least goes some way to explaining why it behaves as it does.


Many thanks,
Bart