Azure sql database - How to set default database

Nguyen, Hoa 331 Reputation points
2021-05-24T22:41:21.037+00:00

We have AD groups as logins, and a few sql logins in our databases. They all have to switch to the app database as when they use VS or SSMS, they default to the master database.

alter login xxx with default_database = [YY]

Msg 40517, Level 16, State 1, Line 1
Keyword or statement option 'default_database' is not supported in this version of SQL Server.

Completion time: 2021-05-24T17:38:43.6054597-05:00

Is there a way to set a default database for users in azure sql?

Thank you!

Azure SQL Database
0 comments No comments
{count} votes

Accepted answer
  1. Alberto Morillo 32,711 Reputation points MVP
    2021-05-25T02:55:22.187+00:00

    There is no way to set a default database for a user,you cannot set the default database property for the login on Azure SQL, thus default database will always be [master] database.

    User connecting directly using SSMS o Azure Data Studio needs to specify to which database they will connect. Applications will need to specify the "initial catalog" and specify to which database.

    "Database name must be used when authenticating, cannot use just <default> database"
    "Create SQL user on the master database (this is necessary for login attempt to the <default> database, as with Azure SQL you cannot set the DEFAULT_DATABASE property of the login so it always will be [master] database.)"
    Source: Create SQL Login and SQL User on your Azure SQL DB

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. plitwin 1 Reputation point
    2022-01-23T16:54:11.94+00:00

    Of course, another solution, is to give the user db_datareader permissions to Master. Then they will never get that brain-dead error message when connecting to SQL Database from SSMS.