?
Solved

SQL 2008 Microsoft SQL server, error: 18456

Posted on 2011-09-26
2
Medium Priority
?
630 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 3

Accepted Solution

by:
_-W-_ earned 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

752 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