SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


users not able to access database


users not able to access database

Author
Message
Divine Flame
Divine Flame
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6531 Visits: 2816
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
Lowell
Lowell
SSC Guru
SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)

Group: General Forum Members
Points: 121676 Visits: 41411
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
Gazareth
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11332 Visits: 6068
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
Divine Flame
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6531 Visits: 2816
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
Lowell
SSC Guru
SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)

Group: General Forum Members
Points: 121676 Visits: 41411
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
Divine Flame
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6531 Visits: 2816
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
kevaburg
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7071 Visits: 1045
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
Divine Flame
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6531 Visits: 2816
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
kevaburg
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7071 Visits: 1045
I did miss the post! Never mind! Glad you got it sorted though!
Gazareth
Gazareth
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11332 Visits: 6068
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 :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search