August 3, 2012 at 12:12 pm
Might there be another group with this same set of users that is actually being used to log them in? I don't know how SQL Server figures out which group to use if a user is a member of 2 different windows groups that both have logins. My guess is that one of those groups has sysadmin and one doesn't and the one that is being used is still pointing to that old, deleted DB.
Just a stab in the dark, I'm afraid but it would explain the observations.
August 3, 2012 at 12:34 pm
Tanx David but
the user Z is not a member of any other group .the interesting thing is that the error thrown to the user differs from the error loggeg in the error log!
this is the error message thrown when trying to connect with the win group login:
"Error 4064 'cannot open user default database' "
but this is the error logged in the sql error log:
"Login failed for user 'MyDomain\Z'. Reason: Failed to open the database specified in the login "
Pooyan
August 3, 2012 at 12:39 pm
What tool are they using to connect? Is it going through an ODBC or other type of connection that might specify an incorrect database. What happens if they try to connect with SSMS?
August 3, 2012 at 12:50 pm
all I'm saying is with using SSMS at the moment.
the error state logged in the error log is 40
in this blog :http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx
it says that "Other error states indicate an internal error and may require assistance from CSS" any idea/
Pooyan
August 3, 2012 at 1:08 pm
OK, I'm stumped. Can you send screenshots of the login properties tabs for that login, including the roles display for the default database?
August 3, 2012 at 2:07 pm
General Tab:Nothing specific just the windows group name for the windows authentication
Server Role Tab:No other setting for the login but set the sysadmin server role
User Mapping:nothing set
Securables : nothing set
Pooyan
August 3, 2012 at 2:14 pm
On the user mapping tab, map the user to the public role in the default database and try the login again.
August 3, 2012 at 2:15 pm
On the user mapping tab, map the user to the public role in the default database and try the login again.
Sorry, hit 'enter' twice...
August 3, 2012 at 3:07 pm
it is set by default.the server role is set to sysadmin so it should have full permission on every database
it's realy confusing me:crazy:
Pooyan
August 3, 2012 at 3:27 pm
I'd try to map to public, but it's up to you. I'd test it out myself, but I'm not in a place where I can do that now. That was my last idea, unfortunately.
August 3, 2012 at 3:58 pm
sure thanks for your time David
Pooyan
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply