本文针对 Dynamics 365 的日常运维和管理需求，整理些实用的 SQL 查询脚本

1. 查询指定实体下的所有系统视图

该查询用于快速检索 Dynamics 365 中特定实体（如客户、联系人）下配置的所有系统视图

-- Example: Query all views and their Ids under the Account entity
SELECT
    v.SavedQueryId AS 'View Id',
    v.Name AS 'View name',
    v.returnedtypecode AS 'Owning entity'
FROM
    SavedQuery v
WHERE
    returnedtypecode = 'account'

2. 检索系统报表清单与元数据

通过查询报表主表，可以获取环境中所有报表的详细信息，包括创建人、创建时间及文件名称，常用于报表资产盘点或清理闲置报表

SELECT reportid,
       name,
       filename,
       description,
       owneridname,
       componentstatename,
       createdon,
       createdbyname
FROM   report;

3. 查询 Annotation 容量

-- Warning: May freeze if data is large
-- 1 MB = 1024 * 1024 Bytes = 1,048,576 Bytes
SELECT SUM(filesize) / 1048576 AS TotalFileSizeMB
FROM   annotation;

4. 查询特定安全角色的所有分配用户

此脚本用于审计，清晰列出拥有某个特定安全角色（如“售前顾问”）的所有用户

SELECT
    systemuser.fullname AS UserFullName,
    systemuser.domainname AS DomainName,
    systemuser.systemuserid AS UserId,
    role.name AS RoleName
FROM
    systemuserroles
INNER JOIN
    systemuser ON systemuserroles.systemuserid = systemuser.systemuserid
INNER JOIN
    role ON systemuserroles.roleid = role.roleid
WHERE
    role.name = 'Pre-sales' -- Security role name

Query result:

UserFullName DomainName UserId RoleName
Zhang San xxx.. Pre-sales
Li Si xxx.. Pre-sales

5. 查看用户分配了哪些安全角色

SELECT
    su.fullname AS UserFullName,
    su.domainname AS DomainName,
    su.systemuserid AS UserId,
    STRING_AGG(r.name, ', ') AS RoleNames
FROM
    systemuserroles sur
INNER JOIN
    systemuser su ON sur.systemuserid = su.systemuserid
INNER JOIN
    role r ON sur.roleid = r.roleid
GROUP BY
    su.fullname,
    su.domainname,
    su.systemuserid
ORDER BY
    su.fullname

查询结果：

UserFullName DomainName UserId RoleName
Zhang San xxx Pre-sales, Sales Manager
Li Si xxx Pre-sales, Sales Director, System Administrator

如果本文对你有所帮助，可以请我喝杯咖啡

（完）