How to list permissions on a schema?

Question

In SSMS, if you follow the path [Database] > Security > Schemas and view any schema properties, you have a tab "permissions" that list all the permissions that every user have on that specific schema.

I would like to make a query that gives me the same output than that tab. I tried using sys.database_principals, database_permissions and sys_objects, but I can't retrieve the permissions linked to schemas.

Answer 1

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

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

Answer 2

Also, if you would need the exact queries as SSMS Launches them, they are not that readable, and several are used to get the result shown in SSMS.

The base query when opening the permissions on my test schema:

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'

Resulting in a row for each permission granted

Grantee GranteeType
guest   2
bla 2
bla 2

(Queries used to grant)

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

The second query, for each principal, bla in my example

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'

Not really readable result

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

Combining the two queries, you could get something a bit more readable

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

Resulting in:

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

Answer 3

With the following command I managed to get a more correct response :

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'

The thing is that Scott's answer I think the link between sys.database_permissions and sys.database_principals is incorrect. Why joining over major_id since that represents the schema and not the grantee (principal).

But thanks Scott !! your answer helps a lot !