Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

users not able to access database Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 5:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 1,312, Visits: 2,489
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
Post #1430888
Posted Thursday, March 14, 2013 5:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1430897
Posted Thursday, March 14, 2013 5:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 1,872, Visits: 2,995
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
Post #1430898
Posted Thursday, March 14, 2013 7:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 1,312, Visits: 2,489
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
Post #1430978
Posted Thursday, March 14, 2013 8:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1431003
Posted Tuesday, March 19, 2013 7:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 1,312, Visits: 2,489
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
Post #1432667
Posted Wednesday, March 20, 2013 3:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
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.
Post #1433080
Posted Wednesday, March 20, 2013 4:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 1,312, Visits: 2,489
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
Post #1433111
Posted Wednesday, March 20, 2013 4:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
I did miss the post! Never mind! Glad you got it sorted though!
Post #1433113
Posted Wednesday, March 20, 2013 7:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 1,872, Visits: 2,995
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
Post #1433190
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse