MSSQL 2016: Cannot open user default database. Login failed

Microsoft.SQLServer.2016.Cannot_open_user_default_database._Login_failed_1_5_Rule (Rule)

When a client connects to a SQL Server instance without specifying a database context, the default database defined for its login is used. If that database is unavailable for any reason, the above message appears.

Knowledge Base article:

Summary

Note: If the login is a member of one or more Windows groups that are also defined as logins within SQL Server, the default database for one of those groups may be used. There is not a reliable way to predict which of the default databases will be chosen during a login attempt. Therefore it is important to check group membership for the login account and then check the default database set for all groups to see that they are all available.

Resolutions

You need to either make the default database accessible or avoid using that particular database. Your options include:

For more information about default databases, see "sp_defaultdb" in Books Online and the Microsoft Knowledge Base article 307864.

Change your connection string to always specify a particular database.
Verify that the default database defined for each login still exists and is accessible. Review the SQL Server error log to help determine why a particular database is not accessible.
Use a tool such as OSQL to specify a particular database during the initial connection. Once you are connected, execute sp_defaultdb to change the default database for your login to a database that is currently accessible.
Log in with an account that is a member of the sysadmin or securityadmin fixed server roles, and execute sp_defaultdb to change the default database for the failing login to an accessible database. For more information about default databases, see "sp_defaultdb" in Books Online and the Microsoft Knowledge Base article 307864.

Overrideable Parameters

Name	Description	Default Value
Enabled	Enables or disables the workflow.	Yes
Priority	Defines Alert Priority.	1
Severity	Defines Alert Severity.	2

Element properties:

Target

Microsoft.SQLServer.2016.DBEngine

Category

EventCollection

Enabled

True

Event_ID

4064

Event Source

$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/ServiceName$

Alert Generate

True

Alert Severity

Error

Alert Priority

Normal

Remotable

True

Alert Message

MSSQL 2016: Cannot open user default database. Login failed

{0}

Event Log

Application

Comment

Mom2016ID='{4FFA6A6A-3E49-468E-8C4A-DF5029908802}';MOM2016GroupID={467ECC75-C5DA-42BD-955C-A73BBB51AF74}

Member Modules:

ID	Module Type	TypeId	RunAs
_F6DA1507_12AF_11D3_AB21_00A0C98620CE_	DataSource	Microsoft.Windows.EventProvider	Default
GenerateAlert	WriteAction	System.Health.GenerateAlert	Default

Source Code:

<Rule ID="Microsoft.SQLServer.2016.Cannot_open_user_default_database._Login_failed_1_5_Rule" Target="SQL2016Core!Microsoft.SQLServer.2016.DBEngine" Enabled="true" ConfirmDelivery="true" Remotable="true" Comment="Mom2016ID='{4FFA6A6A-3E49-468E-8C4A-DF5029908802}';MOM2016GroupID={467ECC75-C5DA-42BD-955C-A73BBB51AF74}">

  <Category>EventCollection</Category>

  <DataSources>

    <DataSource ID="_F6DA1507_12AF_11D3_AB21_00A0C98620CE_" Comment="{F6DA1507-12AF-11D3-AB21-00A0C98620CE}" TypeID="Windows!Microsoft.Windows.EventProvider">

      <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>

      <LogName>Application</LogName>

      <Expression>

        <And>

          <Expression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQuery>PublisherName</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

                <Value>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/ServiceName$</Value>

              </ValueExpression>

            </SimpleExpression>

          </Expression>

          <Expression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQuery>EventDisplayNumber</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

                <Value>4064</Value>

              </ValueExpression>

            </SimpleExpression>

          </Expression>

        </And>

      </Expression>

    </DataSource>

  </DataSources>

  <WriteActions>

    <WriteAction ID="GenerateAlert" TypeID="SystemHealth!System.Health.GenerateAlert">

      <Priority>1</Priority>

      <Severity>2</Severity>

      <AlertMessageId>$MPElement[Name="Microsoft.SQLServer.2016.Cannot_open_user_default_database._Login_failed_1_5_Rule.AlertMessage"]$</AlertMessageId>

      <AlertParameters>

        <AlertParameter1>Event ID: $Data/EventDisplayNumber$. $Data/EventDescription$</AlertParameter1>

      </AlertParameters>

      <Suppression>

        <SuppressionValue/>

      </Suppression>

    </WriteAction>

  </WriteActions>

</Rule>