Azure SQL DB默认guest账户用途及生产库CONNECT权限撤销咨询

Great question—this is a common point of confusion with Azure SQL DB's built-in principals, so let's break it down clearly.

What's the guest Account Used For? #

The guest user is a built-in database-level principal that exists in every user database (not the master database) in Azure SQL DB. Its core purpose is to enable logins without a dedicated database user to access the database:

• If you create a SQL login in the master database but don't provision a corresponding user in a specific user database, that login will automatically use the guest account to connect—only if the guest account has the CONNECT permission enabled.
• This was originally designed for scenarios where you have a large number of logins that need limited, shared access to a database without creating individual users for each, but it's rarely used in modern production setups.

Should You Revoke CONNECT Permission in Production Databases? #

Short answer: Yes, almost always. Here's why:

• First, note that in modern Azure SQL DB deployments, the guest account's CONNECT permission is disabled by default. You can verify this with this query:

  SELECT name, has_perms_by_name(name, 'DATABASE', 'CONNECT') AS has_connect_permission
  FROM sys.database_principals
  WHERE name = 'guest';
  

  A result of 0 means the permission is revoked (the default state).
• If your database has CONNECT enabled for guest, revoking it aligns with the principle of least privilege:
  • It eliminates an unnecessary access vector, preventing accidental or unauthorized access from logins that weren't explicitly granted access to the database.
  • Reduces the risk of privilege escalation or data exposure, especially if the guest account inherits unintended permissions from the public role (which it does by default).
• To revoke the permission if it's enabled, run this command:

  REVOKE CONNECT TO guest;
  

• The only exception: If you have a specific, documented business need for logins to access the database without dedicated users, you can keep CONNECT enabled—but ensure you strictly limit the guest account's other permissions (e.g., only grant SELECT on specific tables, never write permissions).

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