• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

SQL 2008 Microsoft SQL server, error: 18456

I upgraded my SQL Server 2005 Standard Edition to SQL 2008 Standard Edition (10.0.400) on a Windows 2003 R2 Server SP 2 the other day.  This machine had two developers which had local admin to the server which in turn gave them local SA equivalent to the SQL instance when it was SQL 2005.
The upgrade was successful with no errors, but when either of the two users try to login with their domain account they get the following error.
Microsoft SQL server, error: 18456
Now I made sure that there windows domain account had DBO access to the database that they needed access to.  Unfortunately, this did not resolve the problem.  But when I gave the SA equivalent they were able to connect.  Since I am like any other DBA I don’t want to give them SA equivalent.
However, I have found that if I removed there group (Windows\GroupName) from the local admin group from the server then they were able to connect to the SQL instance without SA equivalent.
 These two users need local admin to the server but they don’t need SA equivalent.   If I grant them local admin to the server they get the error message above.  But when I remove them from local admin they can connect to the SQL instance, how do I resolve this problem?
1 Solution
Add them back to local admin & add them to the new SQLServerMSSQLUser$ group. If that does not give them access, make sure they have access to the Master Database and try adding them to sysadmin on the instance.
RayManAaaAuthor Commented:
Thanks, that worked!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now