question

NguyenHoa-7216 avatar image
1 Vote"
NguyenHoa-7216 asked RobKraft-6678 edited

Azure sql database - How to set default database

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

plitwin-4497 avatar image
0 Votes"
plitwin-4497 answered RobKraft-6678 edited

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your contribution and visiting Microsoft QA forums!

0 Votes 0 ·

Unfortunately we can't alter role db_datareader in master on SQL Azure Database either.

0 Votes 0 ·