18456 error with state 38

Question

18456 error with state 38

giri10488

Right there with Babe

Points: 729
More actions
July 31, 2015 at 11:13 am

#300907

Login failed for user ''. Reason: Failed to open the explicitly specified database. [CLIENT:xx.xx.xx.xx]
No missing database & having sysadmin permission.
can u pls give me reason why this error coming

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

this is usually because the user was once assigned to have a default database that has been renamed or deleted. it's possible that the database exists, but permissions prevent the user from accessing it(not a user int he db anymore)

changing the user to have a default database of master is probably the best solution.

there's a legacy stored proc, but also the updated ALTER command to do the same thing: you can do it to a windows or sql user.

EXEC sp_defaultdb @loginame='ClarkKent', @defdb='master'

ALTER LOGIN [MyDomain\Lowell] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

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!

lmarkum SSCertifiable Points: 7989 More actions · Answer 2

I am getting this same error. My situation might be different from the person who originally posted. Not sure. I restored a database from one SQL instance to another on a different box. The default database for the user and login are the same on both SQL instances. The User is in the db_owner role in both SQL instances. How do I resolve this error I am getting?

lmarkum SSCertifiable Points: 7989 More actions · Answer 3

So the query below from another forum clued me in to the problem. I had mistyped the database name in my restore script when I restored it to another sql instance. I typed in the correct database name in the query and no results were returned, so I checked the spelling. Once I corrected the database name, permissions were fine.

SELECT state_desc, user_access_desc, is_auto_close_on, is_in_standby

FROM sys.databases

WHERE name = N'database_name'