users not able to access database

Question

Post reply

users not able to access database

Divine Flame

SSCoach

Points: 15943
More actions
March 14, 2013 at 5:22 am

#291055

Hi Friends,
We are facing an issue on one of our servers. We have some domain users in our database. These users are provided access to the database as the db_owner.
However whenever they try to connect they get the error:
Error: 18456, Severity: 14, State: 38.
Login failed for user XXXX. Reason: Failed to open the explicitly specified database. [CLIENT: XX.XX.XX.XX]
After seeing this error message I checked the AutoClose property for the database as it was the first thing I wanted to check & found that it was ON so I turned it OFF.
Still we are getting the same error when trying to login. Has anybody else faced the same issue?
Any help is appreciated :-).
Sujeet Singh

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

check the default database for that login;

if the database was deleted, then you'll see that error, and need to reassign the default login, probably to master.

so as an example, if i create a login [ClarkKent] with a default database of [SandBox], and then later drop (or rename?) the database [SandBox], you would see this behavior.

the fix is fast and easy:

ALTER LOGIN [ClarkKent] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

Hi, have you checked they're connecting to the database you think they are?

You can grab the info from profiler - User Error Message event.

Just to rule out a misspelt connection string etc.

Cheers

Divine Flame SSCoach Points: 15943 More actions · Answer 3

Lowell (3/14/2013)
check the default database for that login;
if the database was deleted, then you'll see that error, and need to reassign the default login, probably to master.
so as an example, if i create a login [ClarkKent] with a default database of [SandBox], and then later drop (or rename?) the database [SandBox], you would see this behavior.
the fix is fast and easy:
ALTER LOGIN [ClarkKent] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

Thanks Lowel for taking time.

I have checked the default_database for these users & it is already set to master, so that's not an issue.

Another thing I see, when I go to database-->Properties-->Permissions-->Username(actuly a windows group)

The effective permission tab provides this message :

"Cannot execute as the server principal because the principal XXX does not exist, this type of principal cannot be impersonated or you do not have permission."

I think it is because we can not impersonate a windows group. Am I right?

Sujeet Singh

Lowell SSC Guru Points: 323518 More actions · Answer 4

ok if it's a user who exists in a windows group, and doesn't have an individual login in SQL, then it's the issue Gazareth identified;

I assume the user are connecting with an application; that application has built a connection string with either a misspelled or non existent database in the connection string;

An example connection string:

Dim mySqlConnectionFormat As String = "data source=DEV223;
initial catalog=SandBox;
Trusted_Connection=True;
Timeout=600;
Workstation ID=GhostInTheMachine;
Application Name=HaxxorPadPlusPlus;"

so Following what Gazareth said, you need to track down how the connection is built, and get the app to fix it, i'm thinking.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Divine Flame SSCoach Points: 15943 More actions · Answer 5

Thanks Lowell & Gazareth. We were able to track down the problem last week. It was collation of the SQL Server instance that was playing its role over there. Due to case-sensitive settings it was not recognizing the database.

All fixed. Thanks for your time :-).

Sujeet Singh

kevaburg SSCoach Points: 18131 More actions · Answer 6

I know you have set the defalt database to master but that doesn't mean the users are correctly mapped to the database(s) they need access to. It might be worth checking the "MAPPING" entry found in the properties for the user/group that is having trouble accessing the database.

Divine Flame SSCoach Points: 15943 More actions · Answer 7

kevaburg (3/20/2013)
I know you have set the defalt database to master but that doesn't mean the users are correctly mapped to the database(s) they need access to. It might be worth checking the "MAPPING" entry found in the properties for the user/group that is having trouble accessing the database.

??

I think you missed my post above. I said the problem was resolved last week itself & I have provided the details why it happend at first place.

Thanks anyways kevaburg for your efforts.

Sujeet Singh

kevaburg SSCoach Points: 18131 More actions · Answer 8

I did miss the post! Never mind! Glad you got it sorted though!

Gazareth One Orange Chip Points: 27737 More actions · Answer 9

Divine Flame (3/19/2013)
Thanks Lowell & Gazareth. We were able to track down the problem last week. It was collation of the SQL Server instance that was playing its role over there. Due to case-sensitive settings it was not recognizing the database.
All fixed. Thanks for your time :-).

Ouch! Good spot on the case sensitivity 🙂