Azure SQL Server用户角色差异排查及自定义类型权限问题求助

是否是用户角色导致的问题？ #

有可能，但也存在其他常见诱因，比如对象架构未显式指定、表类型权限不足、脚本批次执行顺序问题等，需要先排查这些点，再确认角色差异。

如何对比两台服务器的用户角色与权限？ #

1. 对比服务器级别角色 #

在dev和prod服务器分别执行以下查询，对比用户所属的服务器角色：

SELECT spr.name AS ServerRole, sp.name AS LoginName
FROM sys.server_role_members srm
JOIN sys.server_principals spr ON srm.role_principal_id = spr.principal_id
JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id
WHERE sp.name = 'YourUserID';

2. 对比数据库级别角色 #

在DBName数据库中分别执行：

SELECT dr.name AS DatabaseRole, dp.name AS UserName
FROM sys.database_role_members drm
JOIN sys.database_principals dr ON drm.role_principal_id = dr.principal_id
JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
WHERE dp.name = 'YourUserID';

3. 对比对象级权限（重点检查表类型） #

查询用户对MyTypeTBL的权限：

SELECT dp.permission_name, dp.state_desc, OBJECT_NAME(major_id) AS ObjectName
FROM sys.database_permissions dp
JOIN sys.database_principals dp_princ ON dp.grantee_principal_id = dp_princ.principal_id
WHERE dp_princ.name = 'YourUserID' AND OBJECT_NAME(major_id) = 'MyTypeTBL';

解决步骤 #

1. 显式指定表类型的架构 #

创建存储过程时，必须明确表类型的架构（比如dbo），避免因默认架构差异导致找不到对象：

CREATE PROCEDURE myStoredProcedure
    @InputParam dbo.MyTypeTBL READONLY
AS
BEGIN
    -- 存储过程逻辑
END

2. 授予用户表类型的EXECUTE权限 #

使用自定义表类型需要EXECUTE权限，即使用户能创建表类型，也可能缺少此权限。执行：

GRANT EXECUTE ON TYPE::dbo.MyTypeTBL TO [YourUserID];

3. 确认脚本批次执行顺序 #

确保创建表类型的脚本和存储过程脚本在同一个批次执行，或者表类型创建完成后再执行存储过程脚本（避免因批次隔离导致对象未被识别）。

4. 检查用户默认架构 #

对比dev和prod中用户的默认架构：

SELECT name, default_schema_name FROM sys.database_principals WHERE name = 'YourUserID';

如果prod的默认架构与dev不同，要么修改默认架构，要么在所有对象引用中显式指定架构。

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