Setup for demonstration:
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 for the question:
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
Demonstration cleanup
USE tempdb
GO
DROP DATABASE listschema
Query result:
--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 |