MSDE revokes database access
Posted on 2007-08-09
I developed a commercial application that utilises an MSDE database as the backend.
The installation process installs a separate instance of MSDE and then generates the database through SQL Scripts. Part of the installation process is to create SQL logins for the services that run on the server to be able to connect to the database. These are then assigned access to stored procedures through roles.
A more and more common occurance amongst my clients (I believe about 10-20% are currently effected) is that these service logins suddenly, and without apparent cause, have their access to the database revoked (or possibly just screwed up).
The message that occurs is "Cannot open database requested in login 'GlobaliQProperty'. Login fails.
Login failed for user 'FusionService'.".
As I mentioned this is happening often enough for it not to be coincidence or to do with the particular setup in a client's office. It occurs on both XP and Server 2003 machines (possibly also 2000 and Server 2000 but I can't remember any off the top of my head) and, try as I might, I can't find any kind of consistent cause of this problem.
The current solution is that there is a part of the installation that repairs the logins (using the master stored proc sp_change_users_login with @Action set to AUTO_FIX and @UserNamePattern set to the broken login) and requires the user repairing the installation on their server. The problem with this is that most of the users of the system don't have access to their server and need to call in their IT guys (at significant cost) to perform this task. While it's not fixed the system doesn't work.
This one has been an issue for me for around 2 years and has baffled every DBA and developer I've ever asked about it.
Hoping someone can help!