如何为Azure SQL Server中的AAD用户授予全数据库管理员权限

嘿，根据你描述的场景，要让这个AAD用户拿到所有数据库的完全访问权限，有两个实用方案，你可以根据实际权限需求来选：

方案1：设为Azure AD服务器管理员（最高权限） #

这是最直接的方式，用户会拿到整个SQL Server实例的完全控制权——包括所有数据库的管理权限、创建/删除数据库、配置服务器级别的设置等等。操作得在Azure Portal里完成：

• 登录Azure Portal，找到你的SQL Server资源
• 在左侧导航栏的「设置」分组里找到「Azure Active Directory」（或者直接搜「管理员」）
• 点击「添加管理员」，搜索并选中目标AAD用户，保存即可

方案2：授予所有数据库的db_owner权限（数据库级最高权限） #

如果不想给服务器级的最高权限，只需要用户拥有所有数据库的完全访问（但不能管理服务器本身），可以按以下步骤操作：

步骤1：创建AAD服务器登录 #

首先得在master数据库里创建对应的服务器登录，这样才能跨数据库授予权限：

USE [master]
GO
CREATE LOGIN [usename_emaildomain#EXT#@mydirectory.onmicrosoft.com] FROM EXTERNAL PROVIDER;
GO

步骤2：给现有数据库批量添加db_owner角色 #

先授予用户查看所有数据库的权限，再用循环给每个现有数据库添加db_owner角色（系统数据库可以根据需要选择是否包含）：

-- 授予查看所有数据库的权限
GRANT VIEW ANY DATABASE TO [usename_emaildomain#EXT#@mydirectory.onmicrosoft.com];
GO

-- 为所有非系统数据库添加db_owner角色
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 + ']; 
             IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''usename_emaildomain#EXT#@mydirectory.onmicrosoft.com'')
             BEGIN
                 CREATE USER [' + 'usename_emaildomain#EXT#@mydirectory.onmicrosoft.com' + '] FROM EXTERNAL PROVIDER;
                 EXEC sp_addrolemember ''db_owner'', [' + 'usename_emaildomain#EXT#@mydirectory.onmicrosoft.com' + '];
             END')
    FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor
GO

步骤3：自动给新创建的数据库加权限 #

如果希望未来新建的数据库也自动给这个用户db_owner权限，可以在master库创建一个服务器级触发器：

USE [master]
GO
CREATE TRIGGER AutoAddDBOwnerForAADUser
ON ALL SERVER 
FOR CREATE_DATABASE
AS
BEGIN
    DECLARE @dbname NVARCHAR(128) = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)')
    EXECUTE('USE [' + @dbname + ']; 
             IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''usename_emaildomain#EXT#@mydirectory.onmicrosoft.com'')
             BEGIN
                 CREATE USER [' + 'usename_emaildomain#EXT#@mydirectory.onmicrosoft.com' + '] FROM EXTERNAL PROVIDER;
                 EXEC sp_addrolemember ''db_owner'', [' + 'usename_emaildomain#EXT#@mydirectory.onmicrosoft.com' + '];
             END')
END
GO

注意：记得把所有代码里的usename_emaildomain#EXT#@mydirectory.onmicrosoft.com替换成你目标AAD用户的完整用户主体名称（UPN）哦。

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