如何在Azure SQL数据库中验证登录账户的有效性？

Great question—this is a super common headache when validating non-sysadmin logins for Azure SQL Database, especially since the default master database approach falls flat and using admin accounts to check permissions can run afoul of EULA terms. Let’s break down a few compliant, practical solutions:

方法1：通过错误类型区分登录有效性 #

The key here is recognizing that Azure SQL returns distinct error messages for invalid logins vs. valid logins that lack access to the default master database. You can leverage this to validate the login without accessing any unauthorized resources.

First, let’s recap the error you’re seeing when using a valid non-sysadmin user without specifying a database:

sqlcmd -U xxxUser -S xxxdatabases.database.windows.net
Password:
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : The server principal "xxxdatabases" is not able to access the database "master" under the current security context..
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user 'xxxdatabases'..
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Cannot open user default database. Login failed..

Notice that this error mentions cannot open user default database—this means the username/password is valid, the user just can’t access master. If the login were invalid (wrong password or non-existent user), you’d get a simpler Login failed for user message without the default database context.

You can automate this check with a script (example in PowerShell):

$username = "xxxUser"
$server = "xxxdatabases.database.windows.net"
$password = "yourSecurePassword"

# Capture error output from the connection attempt
$errorOutput = sqlcmd -U $username -S $server -P $password -Q "SELECT 1" 2>&1

# Parse the error to determine login validity
if ($errorOutput -match "Cannot open user default database" -or $errorOutput -match "not able to access the database") {
    Write-Host "✅ Login is valid (user exists and password is correct), but lacks access to master database"
} elseif ($errorOutput -match "Login failed for user") {
    Write-Host "❌ Invalid username or password"
} else {
    Write-Host "❓ Unexpected error occurred during validation"
}

This approach is compliant because you’re only attempting a basic connection and parsing error messages—you’re not accessing any data or using admin privileges to inspect user permissions.

方法2：创建专用的低权限验证数据库 #

For a more reliable, explicit validation flow, create a minimal-purpose database solely for login validation. This ensures you never hit the master access issue and keeps permissions tightly scoped.

Step 1: Set up the validation database (run once as server admin) #

-- Create a dedicated validation database
CREATE DATABASE LoginValidationDB;
GO

USE LoginValidationDB;
GO

-- Create a role for minimal validation access
CREATE ROLE LoginValidator;
GO

-- Grant only the CONNECT permission (no other access needed)
GRANT CONNECT TO LoginValidator;
GO

Step 2: Assign the role to users needing validation #

For each user you want to validate, add them to the LoginValidator role:

ALTER ROLE LoginValidator ADD MEMBER [xxxUser];
GO

Step 3: Validate the login #

Now you can use this database in your connection string to run a trivial check (like SELECT 1) to confirm the login is valid:

sqlcmd -U xxxUser -S xxxdatabases.database.windows.net -d LoginValidationDB -P yourPassword -Q "SELECT 1"

If the command returns 1, the login is valid. If it fails with a login error, the credentials are incorrect.

This method is fully compliant because:

• The validation database has no sensitive data
• Users only get the minimal CONNECT permission
• You don’t need admin privileges to run the validation check

Bonus: Verify the user isn’t a sysadmin #

To ensure the account doesn’t belong to the sysadmin role (per your requirement), you can run this query once the user is connected to the validation database:

SELECT IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin;

A return value of 0 confirms the user is not a sysadmin.

内容的提问来源于stack exchange，提问作者Olle Johansson