1

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.

Improve this question
6
  • Uh, why does the CEO need to log in to SQL Server? And are they failing to log in, or are they failing to get access to a specific database? Maybe post the actual error message you're receiving (and if it's a login failed message, the relevant state # from the error log).
    – Aaron Bertrand
    Nov 6, 2013 at 22:07
  • The specific user is a high-level employee, but I needed something generic that wouldn't give away who they really are. CEO is the first thing that came to mind. This is an enterprise application that they would use to run reports. Maybe they won't use the software as much, but we have others that also need to be changed due to promotions and they do use the software much more.
    – Mike Wills
    Nov 6, 2013 at 22:13
  • Unfortunately because of the complexity of your system (you may want to look into using roles and move users in and out of them rather than "position" users) it's going to be very difficult to debug from here. I have a set of security research scripts here: sqlstudies.com/2013/11/04/… that you could try using to research what permissions your user really has. Can't be sure it will help but it couldn't hurt.
    – Kenneth Fisher
    Nov 6, 2013 at 22:44
  • Well most of us are probably unfamiliar with Cartegraph, but can you show us the error message that SQL Server returns, instead of what the application returns? Again, you can get the whole error message from SQL Server's error log. You should also contact the vendor's support department, as they may have quite specific guidance on permissions.
    – Aaron Bertrand
    Nov 6, 2013 at 23:34
  • They like to hide the real errors. I don't know where those logs would reside. But rebooting worked.
    – Mike Wills
    Nov 7, 2013 at 14:20

1 Answer 1

Reset to default
0

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.