Quick answer: SQL Server error 916 occurs when the login can connect to the SQL Server or Azure SQL logical server but does not have access to the database named in the connection attempt, commonly master. If the application only needs a specific user database, connect directly to that database. If the login genuinely needs master access, create a user for the login in master and grant only the permissions required.
Typical error:
Cannot connect to <servername>.database.windows.net.
The server principal “username” is not able to access the database “master” under the current security context.
Cannot open user default database. Login failed.
Login failed for user ‘testlogin’. (Microsoft SQL Server, Error: 916)
Why does error 916 occur?
In the traditional SQL Server authentication model, a login is authenticated at the server level and then mapped to a database user. The connection may therefore require access to master before the requested user database can be opened. Microsoft documents error 916 as indicating that the login does not have sufficient permissions to connect to the named database.
Solution 1: Connect directly to the required database
If the login only needs access to an application database, you may not need to grant it access to master. In SSMS, select Options <<, open Connection Properties, and enter the required database in Connect to database instead of relying on master as the initial database.
For applications, specify the required database in the connection string, for example:
Server=tcp:<server>.database.windows.net,1433;Initial Catalog=<database>;User ID=<username>;Password=<password>;
Solution 2: Create the login’s user in master
If the login legitimately needs to connect to master, create a database user for the login while connected to master:
USE [master];
GO
CREATE USER [username] FOR LOGIN [loginname];
GO
Do not grant master access simply to make the error disappear. Give the login only the permissions it actually requires. Microsoft notes that logins need a database user mapping for database access, while contained database users can authenticate directly at the database level without a login in master.
Azure SQL: Consider a contained database user
For Azure SQL Database, a contained database user can be a better fit when the identity only needs access to a specific database. A contained user is created in the user database and does not require a corresponding login in master. Microsoft documents this model for both SQL authentication and Microsoft Entra authentication.
USE [YourDatabase];
GO
CREATE USER [username] WITH PASSWORD = 'StrongPasswordHere';
GO
For Microsoft Entra identities, the corresponding pattern is:
CREATE USER [Microsoft_Entra_principal_name] FROM EXTERNAL PROVIDER;
Pro tips:
1. Do not grant access to master unless the application or user actually needs it.
2. For Azure SQL Database applications that only access one database, consider a contained database user and specify the database explicitly in the connection.
3. If the error occurs unexpectedly, check the login-to-user mapping and the connection string’s initial database.
SQL Server error 916 is fundamentally an access problem for the database named in the connection context. Check whether the login should access master or whether the connection should go directly to the intended user database.
See more
Visual Studio Marketplace
SSIS Catalog Migration Wizard
Extend Visual Studio with an easy way to migrate SSIS Catalog projects.
Kunal Rathi
With over 15 years of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.
