User switching accounts, can't log into MS SQL Server anymore First a bit of background, for good or bad, we don't keep one user account attached to one person. The user accounts are matched to their position. For instance the CEO would have a userid of CEO. (Not a real profile, just used as an example).

We have an application that uses the SQL server authentication to restrict what they can access in the application. We use AD groups to control what they can see to make user management easier. We found out in the past we can't rename a user as they can't log into this account. We had to delete the old account and completely create a new different account in order to have them log in. 

Now we have a similar, but different problem. The old "CEO" left and we updated the CEO profile with the new CEO's information, now that account can't log into the application. We have deleted the groups they we have for the application and re-added them. They still can't log in. Based on this article, when you rename a user, the meta data doesn't match so you need to do a little ALTER USER script to fix the user. The suggestion to prevent this in the future would be to use groups much like we are doing now.

Is there a cache of users that might be causing problems, even if we don't authorize by users? How would I flush that cache if that is the case? Is this a problem with SQL Server 2008 as well? We are upgrading to that soon. What else can I look at?

Here is how the user is set up: CREATE LOGIN [DOMAIN\cgUSERS] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

Here is the exact error from the application:


  You don't have proper security rights to read the Cartegraph system tables. Access the the system tables are required to run the application. Please contact your System Administrator.


Another site mentions rebooting the service and/or server. I think I might try that once to see if that fixes our issue.
So after some more researching, we decided to reboot the server after-hours. This morning the person is able to log in. Looks like it is some sort of user caching that was causing the problems. We are going to do our other user moves and see if it works.