Post reply

Error: 18456, Severity: 14, State: 38. - Specified Database - Any way to find out WHICH Database?

  • June 3, 2011 at 9:19 am

    #239353

    Hi all,

    having a not so fun time with a dev. sql server. it has hundreds of databases and the application server creates dynamic connection strings so it's not possible find out that way.

    is there anything I can do on the SQL side to find out which database it's trying to connect to?

    Profiler trace/etc... ?

    Thanks,

    jeff

  • June 3, 2011 at 10:33 am

    #1333802

    profiler trace should do it

    ---------------------------------------------------------------------

  • June 3, 2011 at 10:36 am

    #1333806

    I am not an expert, but sometimes I have used this to find out what database users are conencting to...

    In SQl server management studio

    running sp_who2 active

    and finding your spId in the result set should tell you what database you are connected to.

  • June 3, 2011 at 11:16 am

    #1333831

    Guras (6/3/2011)

    I am not an expert, but sometimes I have used this to find out what database users are conencting to...

    In SQl server management studio

    running sp_who2 active

    and finding your spId in the result set should tell you what database you are connected to.

    i don't think that will help;

    if something is raising Error: 18456, Severity: 14, State: 38. State 38 means user doesn't have access to database it is trying to connect to; part of it's connection string most likely.

    i think the failure is occurring before anything else; i believe the failed login occurs before your spid can be found in the sys tables that sp_who queries against.

    if the setting for logging failed logins is true, you could look at the event log i think; otherwise you'll want a login trace/profiler so you can see where some script is flubbing a login name or password.

    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!

  • June 3, 2011 at 1:29 pm

    #1333900

    if something is raising Error: 18456, Severity: 14, State: 38. State 38 means user doesn't have access to database it is trying to connect to; part of it's connection string most likely.

    i think the failure is occurring before anything else; i believe the failed login occurs before your spid can be found in the sys tables that sp_who queries against.

    if the setting for logging failed logins is true, you could look at the event log i think; otherwise you'll want a login trace/profiler so you can see where some script is flubbing a login name or password.

    In SQL2008, it does NOT mean user doesn't have access to database it is trying to connect to. Check this:

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

  • June 3, 2011 at 1:44 pm

    #1333906

    ahhh ok; definitely more helpful;

    State 38 is:

    Reason: Failed to open the database specified in the login properties.

    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!

  • June 4, 2011 at 10:22 am

    #1334047

    Hey guys,

    thanks for all the suggestions... Wouldn't you know but when i fire up profiler, the damn thing stops erroring!!

    I'll check it out again when (if) it starts up again.

Viewing 7 posts - 1 through 6 (of 6 total)

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