Solved

SQL Server 2008 - Login Fails Using Windows Authentication for Member of Server Administators Group

Posted on 2010-09-12
5
381 Views
Last Modified: 2012-05-10
A coworker wiped SQL Server 2005 and installed SQL Server 2008 on a server named TEST.  I'm trying to login to the new SQL Server 2008, but the login fails.  See the text below.  
1. My user account is a member of TEST\Administrators.
2. The SQL Server is installed in mixed mode.

TITLE: Connect to Server
------------------------------

Cannot connect to GIS-TEST3.

------------------------------
ADDITIONAL INFORMATION:

Login failed for user 'GIS-TEST3\danaj1'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
Comment
Question by:jdana
[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
5 Comments
 
LVL 30

Accepted Solution

by:
Rich Weissler earned 84 total points
ID: 33656843
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 33656951
If you have the sa account at hand, that is much easier - assign the SysAdmin role the Local Admin group.
0
 
LVL 42

Assisted Solution

by:Eugene Z
Eugene Z earned 83 total points
ID: 33656988
you must add 'GIS-TEST3\danaj1' to sql server logins
--there is no builtin\administrators login  in sql 2008 after install anymore (like in sql2005 and before) (way to give local wind admins sql server sa access)
contact the person who installed the sql2008 and ask to grant you sql permissions
0
 
LVL 2

Assisted Solution

by:ColinGordon
ColinGordon earned 83 total points
ID: 33657359
With SQL server 2008 the Builtin Administrators group (local admins) is no longer SQL admns.  The login used to install the database should be able to log in to the database.   If you can't use that login use the procedure in the link in the first answer.  
If you want to operate like in 2005 create a Windows local group - TEST\SQLAdmins - and create a SQL login with the sql admin role for that group.  Then any member of the group will be a SQL admin.
0
 

Author Closing Comment

by:jdana
ID: 33671963
Thanks guys.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

697 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