Hi,

Is there a way to list permissions on a schema granted to a user? Not objects in the schema, but schema itlself?

I tried using

SELECT * FROM fn_my_permissions('MySchemaName', 'SCHEMA')

but return is not what I need. For example, it lists permission SELECT, but not whether permission was granded, revoked, or denied. It is the latter I am interested about.

Any help would be apreciated.

Thank you.

Peter Battelino
BTE Corporation

You might want this, replacing TestSchema and TestUser with the names you want:

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

Rick,

Thank you for your reply. Tried your script, but it always returns empty table, even when I put in administrtative users.

What a m I missing?

Peter Battelino
BTE Corporation

The fixed server roles (like sysadmin or diskadmin) and fixed database roles (like db_owner or db_datareader) don't show up in sys.server_permissions or sys.database_permissions. The permissions for those roles can't be changed, so SQL Server doesn't have to look them up. sys.database_permissions only contains explicit GRANT or DENY statements. But I thought that was what you were looking for. SELECT * FROM sys.database_principals; should list them all out.

Rick,

Thank you for your reply. What I am really looking for (see my original post) is a list permissions on a schema granted to a user.

In other words, let's say I know (database) username and (database) schema name, and I want to know what is permison for, say, SELECT.

What I am looking for is a script which lets me 'plug in' username, schema name, and permission (SELECT), out comes NULL (no permission), or GRANT, or REVOKE, or DENY.

Peter Battelino
BTE Corporation

I may be misunderstanding what you are asking for, but when I tested Rick's script, it seems to me that it returns more or less exactly what you are asking for. There is no place to plug in the permission - it lists all permissions denied or granted, see the script below.

However, REVOKE is missing and will always be missing since REVOKE just removes whatever there used to be.

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

Erland,

Thanks for your time. You are right, your script works fine.

I figured out why Rick's original script yielded empty table: my grantee is a role, not a user. So now Rick's script works for me too.

Thanks again Rick, Erland.

Peter Battelino
BTE Corporation

Oops. I should have considered that possibility.

Not sure if anyone else noticed this too, but Rick's Code seems to join incorrectly. He joins Principals to Permissions via this: Perm.major_ID = Prin.principal_id

However, Perm.major_ID is the Schema ID while Prin.principal_id is the User ID... Rick's code works because when you create a new database with a new scenario, that new SchemaID "TestSchema" is 5. The default UserID of this database will be 5 so it does exist, and the join can happen. Then Rick never references the userID (principal_ID) again, so you wouldn't notice the flaw in logic. However, if you were to add ", USER_NAME(Prin.principal_id)" as a column, then you would realize the join is actually on the default user who ran this script.

When you run this code on your own database, you probably have more schemas then you have users. So, there is no join because there is no instance where a schema ID matches a UserID. This is the reason why we keep getting nothing when we try to run Rick's script.

Instead, try running this script, and swapping out the user names:

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 perm.state_desc, perm.permission_name, 'ON', perm.class_desc,
SCHEMA_NAME(perm.major_id),
'TO', USER_NAME(perm.grantee_principal_id)
FROM sys.database_permissions AS Perm
JOIN sys.database_principals AS Prin
ON Perm.grantee_principal_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