July 25, 2011 at 10:36 am
OS: Windows 2008
SQL Server: SQL Server 2008 w/SP1
There is a default SQL 2005 instance, and a SQL Server 2008 named instance on this box.
there is no issue on SQL 2005 side; but with the SQL 2008 instance: I added the domain group COM\DBA to the sysadmin server role on the SQL 2008 instance, and default database as MASTER; However, none of the members of that group could connect when try to connect from SSMS. IF i added each member's WINDOWS login, it will work.
The error message when trying to connect,saying:
cannot connect to the instance;
cannot open the default database, login failed.
Login failed for user "COM\ABC" ( microsoft SQL Server, Error 4064)
we have another Server having SQL 2008 and added the same group, and it works fine. Only this one is not working as expected. Did i miss anything? I tried to delete and re-add several times, and it still not working.
Please suggest where to look or how to solve this.
TIA.
July 25, 2011 at 10:42 am
DBA in Unit 7 (7/25/2011)
with the SQL 2008 instance: I added the domain group COM\DBA to the sysadmin server role on the SQL 2008 instance, and default database as MASTER; However, none of the members of that group could connect when try to connect from SSMS. IF i added each member's WINDOWS login, it will work.The error message when trying to connect,saying:
cannot connect to the instance;
cannot open the default database, login failed.
Login failed for user "COM\ABC" ( microsoft SQL Server, Error 4064)
Sounds like something is wrong with the default database. You're sure the group has it added as MASTER? You're sure MASTER is up and working (no corruption)?
Try adding the group with a different default database and see if you get the same error.
July 25, 2011 at 10:57 am
Keep in mind MASTER may not be master :crazy:
What's the collation of the instance?
can you post @@version info ?
-- this may help to figure out the issue.
EXEC master..xp_logininfo @acctname = 'domain\username',@option = 'all' -- Show all paths a user gets his auth from
go
EXEC master..xp_logininfo @acctname = 'domain\wingroup',@option = 'members' -- show group members
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 25, 2011 at 12:06 pm
Thanks guys. Here are the info
Select @@VERSION
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
Collation: SQL_Latin1_General_CP1_CI_AS
The master database is up running fine...as I can connect with the individual logins ( after added explicitly);
Did try a different Default DB for the group login, and same error ...
And for the xp_logininfo queries, everything returned correctly: the memebers of the group has the admin privilige through the domain group, and the group listed out all the members that are supposed to be there. ( those are nice scripts to have, ALZDBA, thank you!)
July 25, 2011 at 12:12 pm
is/are the user accounts you tested actual database owner and is such database offline ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 25, 2011 at 12:42 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply