"Cannot open user default database. Login failed"

  • Good Morning Experts,

    User received the below error while trying to connect to an instance

    "Cannot open user default database. Login failed"

    I checked and found that the user is a member of several AD Groups. Could you please advise on how to fix this

  • Check the properties of the login.  What is its default database.  Now, does the login have access to that database?

    John

  • John Mitchell-245523 - Wednesday, November 15, 2017 3:20 AM

    Check the properties of the login.  What is its default database.  Now, does the login have access to that database?

    John

    There are no individual logins. All are AD groups.

  • Then you'll need to do a bit of investigation.  Run xp_logininfo 'DomainName\GroupName', 'members' for each to group to find out which group(s) the user is a member of.  That'll only work if the user's membership isn't nested in a subgroup, so if you don't find the user, ask your AD admin to check what groups the user is a member of.

    John

  • coolchaitu - Wednesday, November 15, 2017 4:06 AM

    John Mitchell-245523 - Wednesday, November 15, 2017 3:20 AM

    Check the properties of the login.  What is its default database.  Now, does the login have access to that database?

    John

    There are no individual logins. All are AD groups.

    AD Groups can't have default databases assigned, thus logins connecting via them will default to master. This either means that the person connecting is specifying a default database in their connection string they do not have access to, or their account has their own login on the SQL Server, which has a default database that (again) they do not have an attached user account for.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Thom A - Wednesday, November 15, 2017 4:14 AM

    AD Groups can't have default databases assigned, thus logins connecting via them will default to master. This either means that the person connecting is specifying a default database in their connection string they do not have access to, or their account has their own login on the SQL Server, which has a default database that (again) they do not have an attached user account for.

    Are you sure about that, Thom?  It's not documented on the CREATE LOGIN page, and I just successfully ran this command on a SQL Server 2014 server:

    CREATE LOGIN [DomainName\GroupName] FROM WINDOWS WITH DEFAULT_DATABASE = John

    You may be confusing this with default schemas not being able to be assigned to users mapped to logins that are Windows groups.

    John

  • John Mitchell-245523 - Wednesday, November 15, 2017 4:33 AM

    Are you sure about that, Thom?  It's not documented on the CREATE LOGIN page, and I just successfully ran this command on a SQL Server 2014 server:

    CREATE LOGIN [DomainName\GroupName] FROM WINDOWS WITH DEFAULT_DATABASE = John

    You may be confusing this with default schemas not being able to be assigned to users mapped to logins that are Windows groups.

    John

    Yep, sorry! You're right. Clearly brain not engaged yet!

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Thom A - Wednesday, November 15, 2017 4:47 AM

    John Mitchell-245523 - Wednesday, November 15, 2017 4:33 AM

    Are you sure about that, Thom?  It's not documented on the CREATE LOGIN page, and I just successfully ran this command on a SQL Server 2014 server:

    CREATE LOGIN [DomainName\GroupName] FROM WINDOWS WITH DEFAULT_DATABASE = John

    You may be confusing this with default schemas not being able to be assigned to users mapped to logins that are Windows groups.

    John

    Yep, sorry! You're right. Clearly brain not engaged yet!

    There are no individual logins. Individual Logins are added to AD groups. These groups are added as server principals.

  • coolchaitu - Wednesday, November 15, 2017 7:08 AM

    Thom A - Wednesday, November 15, 2017 4:47 AM

    John Mitchell-245523 - Wednesday, November 15, 2017 4:33 AM

    Are you sure about that, Thom?  It's not documented on the CREATE LOGIN page, and I just successfully ran this command on a SQL Server 2014 server:

    CREATE LOGIN [DomainName\GroupName] FROM WINDOWS WITH DEFAULT_DATABASE = John

    You may be confusing this with default schemas not being able to be assigned to users mapped to logins that are Windows groups.

    John

    Yep, sorry! You're right. Clearly brain not engaged yet!

    There are no individual logins. Individual Logins are added to AD groups. These groups are added as server principals.

    You will need to find which group the individual login belongs to as the starting point (xp_logininfo 'DomainName\GroupName', 'members')
    Then find that particular group and see what the default database is.  If it is anything that's not master, ensure that database is present and correct

  • I get the list of domain groups for a domain user by opening a cmd window and executing:
    net user /domain theirname

    Also, I generally create a Startup database and assign all logins to that as a default.  It prevents people from creating junk objects in master.  It is also easy to check logins work without checking the status of all databases the login may use.  This technique also forces applications to be responsible for setting a database after connecting and "knowing" which database they are currently working against.

  • anthony.green - Wednesday, November 15, 2017 7:13 AM

    coolchaitu - Wednesday, November 15, 2017 7:08 AM

    Thom A - Wednesday, November 15, 2017 4:47 AM

    John Mitchell-245523 - Wednesday, November 15, 2017 4:33 AM

    Are you sure about that, Thom?  It's not documented on the CREATE LOGIN page, and I just successfully ran this command on a SQL Server 2014 server:

    CREATE LOGIN [DomainName\GroupName] FROM WINDOWS WITH DEFAULT_DATABASE = John

    You may be confusing this with default schemas not being able to be assigned to users mapped to logins that are Windows groups.

    John

    Yep, sorry! You're right. Clearly brain not engaged yet!

    There are no individual logins. Individual Logins are added to AD groups. These groups are added as server principals.

    You will need to find which group the individual login belongs to as the starting point (xp_logininfo 'DomainName\GroupName', 'members')
    Then find that particular group and see what the default database is.  If it is anything that's not master, ensure that database is present and correct

    The individual login belongs to several groups(about 50 groups). How do i proceed?

  • Have you followed this up by checking the default database for those AD groups?

  • coolchaitu - Wednesday, November 15, 2017 3:17 AM

    Good Morning Experts,

    User received the below error while trying to connect to an instance

    "Cannot open user default database. Login failed"

    I checked and found that the user is a member of several AD Groups. Could you please advise on how to fix this

    Or , may be the database itself is deleted or you are trying to connect to different server where this database doesn't exist.

Viewing 13 posts - 1 through 13 (of 13 total)

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