如何为Azure AD组配置Azure SQL Server非管理员访问权限？

核心误区说明 #

Azure SQL Server的**Contributor角色（Azure RBAC）**仅用于管理Azure层面的服务器资源（如修改服务器配置、重启服务等），不提供数据库的访问权限——这就是你配置Contributor角色后无法连接数据库的原因。

正确配置步骤 #

1. 创建Azure AD组的SQL Server登录名

使用服务器管理员账号（Azure AD管理员或SQL管理员）连接SQL Server，执行以下T-SQL命令，将Azure AD组注册为SQL Server的合法登录主体：

CREATE LOGIN [你的Azure AD组名称] FROM EXTERNAL PROVIDER;

2. 授予组访问所有数据库的权限

在master数据库中执行以下脚本，自动为所有非系统数据库创建AD组对应的用户，并授予读写权限：

-- 允许组查看服务器上所有数据库
GRANT VIEW ANY DATABASE TO [你的Azure AD组名称];

-- 遍历所有非系统数据库，创建用户并授予权限
DECLARE @dbname NVARCHAR(128)
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 创建数据库用户
    EXECUTE('USE [' + @dbname + ']; CREATE USER [你的Azure AD组名称] FOR LOGIN [你的Azure AD组名称];')
    -- 授予读取权限
    EXECUTE('USE [' + @dbname + ']; ALTER ROLE db_datareader ADD MEMBER [你的Azure AD组名称];')
    -- 授予写入权限
    EXECUTE('USE [' + @dbname + ']; ALTER ROLE db_datawriter ADD MEMBER [你的Azure AD组名称];')
    -- 若需要修改数据库结构权限，可添加此行：ALTER ROLE db_ddladmin ADD MEMBER [你的Azure AD组名称];
    FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor

3. 验证连接

让AD组内用户打开SSMS，选择Azure Active Directory - 密码认证方式，输入个人Azure AD账号密码，即可连接SQL Server并访问所有数据库。

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