Skip to main content
improved readability via better formatting
Source Link
Hannah Vernon
  • 70.5k
  • 22
  • 177
  • 321
    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

    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

    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         |
state_descpermission_name(No column name)class_desc(No column name)(No column name)(No column name)
DENYINSERTONSCHEMATestSchemaTOTestUser
GRANTSELECTONSCHEMATestSchemaTOTestUser
    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

    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

    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         |
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
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
USE tempdb
GO
    
DROP DATABASE listschema
state_descpermission_name(No column name)class_desc(No column name)(No column name)(No column name)
DENYINSERTONSCHEMATestSchemaTOTestUser
GRANTSELECTONSCHEMATestSchemaTOTestUser

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         |
--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         |

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:

    | 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         |
Source Link

Using the accepted answer from List schema permissions, this would probably give you what you want.

--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         |