How to set Azure SQL Database default_database to NewDatabase

Question

How do we change the default database for a new user to our [new database] instead of master.

we are using Azure SQL/ SQL database and getting the error 'Keyword or statement option 'default_database' is not supported in this version of SQL Server.'

Is there another work around for this?

Answer 1

Hi @Robert Lang a
Thank you for your question.

The error you're seeing is expected behavior in Azure SQL Database. Unlike SQL Server or Azure SQL Managed Instance, Azure SQL Database doesn't support the DEFAULT_DATABASE option for logins, so attempting to use ALTER LOGIN ... WITH DEFAULT_DATABASE returns:

Keyword or statement option 'default_database' is not supported in this version of SQL Server.

Since this is a platform limitation, there isn't a supported way to change the default database for a login.

The recommended approach is to specify the target database in the client connection string using the Database (or Initial Catalog) parameter. This ensures that the application or client connects directly to NewDatabase instead of master.

For example:

Server=tcp:<server>.database.windows.net,1433;
Database=NewDatabase;
User ID=<user>;
Password=<password>;
Encrypt=True;

If you're creating new users, another recommended option is to use contained database users, which are created directly within the target database. This is the preferred authentication model for many Azure SQL Database scenarios.

For more information, please refer to the following Microsoft documentation:

• Authorize database access to Azure SQL Database
• Contained database users in SQL Server and Azure SQL Database

I hope this helps clarify the behavior. If you're connecting through a specific client (SSMS, Azure Data Studio, .NET, JDBC, ODBC, etc.), let us know and we can provide an example connection string for that scenario.

Answer 2

Hello @Robert Lang

The DEFAULT_DATABASE option isn't supported in Azure SQL Database, so you can't change a user's or login's default database from master to another database as you would in SQL Server or Azure SQL Managed Instance.

The supported approach is to specify the target database in the client connection. For example:

• In your connection string, set Database=NewDatabase (or Initial Catalog=NewDatabase).
• In SQL Server Management Studio (SSMS), click Options → Connection Properties and set Connect to database to NewDatabase before connecting.

If you're using contained database users (the recommended authentication model for Azure SQL Database), make sure the user is created within NewDatabase and connect directly to that database.

Please "Accept the Answer" if this information helped you. This will help us and others in the community as well.