Solved

SQL 2008 Microsoft SQL server, error: 18456

Posted on 2011-09-26
2
593 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:RayManAaa
2 Comments
 
LVL 3

Accepted Solution

by:
_-W-_ earned 500 total points
ID: 36601373
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.
0
 

Author Closing Comment

by:RayManAaa
ID: 36709622
Thanks, that worked!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now