0

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.

2

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         |
  • Thanks. I already tried that query but the output was empty. I tried it again after creating TestSchema and TestUser and now it returns me the same output as you. I don't understand why my permissions on other schemas won't show... – Kimo Feb 12 at 15:22
  • @Kimo - you'll probably need to update your question and include a minimal and verifiable example of the problem - we need information (tsql scripts) similar to my example. Since we can't see what's on your server, we're just guessing. – Scott Hodgin Feb 12 at 15:26
  • I updated my question – Kimo Feb 12 at 15:38
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.