Azure sql database - How to set default database

Question

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!

Answer 1

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

Answer 2

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.