Como listar permissões em um esquema?

Usando a resposta aceita de List schema permissions , isso provavelmente forneceria o que você deseja.

--demo setup
CREATE DATABASE listschema
GO

USE listschema
GO

CREATE SCHEMA TestSchema
GO

CREATE USER TestUser WITHOUT LOGIN
GO

GRANT SELECT
    ON SCHEMA::TestSchema
    TO TestUser

DENY INSERT
    ON SCHEMA::TestSchema
    TO TestUser
GO

--The actual query
SELECT state_desc
    ,permission_name
    ,'ON'
    ,class_desc
    ,SCHEMA_NAME(major_id)
    ,'TO'
    ,USER_NAME(grantee_principal_id)
FROM sys.database_permissions AS PERM
JOIN sys.database_principals AS Prin
    ON PERM.major_ID = Prin.principal_id
        AND class_desc = 'SCHEMA'
WHERE major_id = SCHEMA_ID('TestSchema')
    AND grantee_principal_id = user_id('TestUser')
    --AND    permission_name = 'SELECT'
GO

--cleanup
USE tempdb
GO

DROP DATABASE listschema

| state_desc | permission_name | (No column name) | class_desc | (No column name) | (No column name) | (No column name) |
|------------|-----------------|------------------|------------|------------------|------------------|------------------|
| DENY       | INSERT          | ON               | SCHEMA     | TestSchema       | TO               | TestUser         |
| GRANT      | SELECT          | ON               | SCHEMA     | TestSchema       | TO               | TestUser         |

Além disso, se você precisar das consultas exatas à medida que o SSMS as inicia, elas não são tão legíveis e várias são usadas para obter o resultado mostrado no SSMS.

A consulta base ao abrir as permissões no meu esquema de teste:

exec sp_executesql N'SELECT
grantee_principal.name AS [Grantee],
CASE grantee_principal.type WHEN ''R'' THEN 3 WHEN ''A'' THEN 4 ELSE 2 END - CASE ''database'' WHEN  ''database'' THEN 0 ELSE 2 END AS [GranteeType]
FROM
sys.schemas AS s
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=s.schema_id AND prmssn.minor_id=0 AND prmssn.class=3
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(s.name=@_msparam_0)',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'TEST'

Resultando em uma linha para cada permissão concedida

Grantee GranteeType
guest   2
bla 2
bla 2

(Consultas usadas para conceder)

GRANT EXECUTE ON SCHEMA :: test TO bla;
GRANT INSERT ON SCHEMA :: test TO bla;
GRANT INSERT ON SCHEMA :: test TO guest;

A segunda consulta, para cada principal, bla no meu exemplo

exec sp_executesql N'SELECT
ascii(prmssn.state) AS [PermissionState],
null AS [Code],
grantor_principal.name AS [Grantor],
prmssn.type AS [SqlCodePP],
CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type FROM sys.database_principals AS oc WHERE oc.principal_id = prmssn.major_id) WHEN ''R'' THEN CASE prmssn.class WHEN 4 THEN 201 ELSE 301 END WHEN ''A'' THEN 202 ELSE CASE prmssn.class WHEN 4 THEN 200 ELSE 101 END END ELSE prmssn.class END AS [HiddenObjectClass]
FROM
sys.schemas AS s
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=s.schema_id AND prmssn.minor_id=0 AND prmssn.class=3
INNER JOIN sys.database_principals AS grantor_principal ON grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(grantee_principal.name=@_msparam_0)and((s.name=@_msparam_1))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'bla',@_msparam_1=N'TEST'

Resultado não muito legível

PermissionState Code    Grantor SqlCodePP   HiddenObjectClass
71              NULL     dbo       EX           3
71              NULL     dbo       IN           3

Combinando as duas consultas, você pode obter algo um pouco mais legível

DECLARE @SCHEMA varchar(255) = 'test'
SELECT DISTINCT
CASE WHEN prmssn.state = 'D' then 'Deny'  WHEN prmssn.state = 'R' THEN 'REVOKE' WHEN prmssn.state = 'G' THEN 'Grant'   ELSE  ' Grant With Grant Option' end as permissionstate,
grantor_principal.name AS [Grantor],
prmssn.permission_name AS [name],
class_desc,Grantees.grantee
FROM
sys.schemas AS s
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=s.schema_id AND prmssn.minor_id=0 AND prmssn.class=3
INNER JOIN sys.database_principals AS grantor_principal ON grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
INNER JOIN (SELECT
grantee_principal.name AS [Grantee]
FROM
sys.schemas AS s
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=s.schema_id AND prmssn.minor_id=0 AND prmssn.class=3
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(s.name= @SCHEMA)) as Grantees
on Grantees.grantee = grantee_principal.name
WHERE
((s.name=@SCHEMA))

Resultando em:

permissionstate Grantor name    class_desc  grantee
Grant            dbo    EXECUTE SCHEMA      bla
Grant            dbo    INSERT  SCHEMA      bla
Grant            dbo    INSERT  SCHEMA      guest

Com o seguinte comando consegui obter uma resposta mais correta:

SELECT state_desc
    ,permission_name
    ,'ON'
    ,class_desc
    ,SCHEMA_NAME(major_id)
    ,'TO'
    ,USER_NAME(grantee_principal_id)
FROM sys.database_permissions AS PERM
JOIN sys.database_principals AS Prin
    ON PERM.grantee_principal_id = Prin.principal_id
        AND PERM.class_desc = 'SCHEMA'

A coisa é que a resposta de Scott eu acho que a ligação entre sys.database_permissionse sys.database_principalsestá incorreta. Por que aderir , uma major_idvez que representa o esquema e não o donatário (principal).

Mas obrigado Scott !! sua resposta ajuda muito!