List schema permissions

Question

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

---

PMBT

Answer 1

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 Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

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

---

PMBT

Answer 3

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 Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 4

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

---

PMBT

Answer 5

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 Sommarskog, SQL Server MVP, [e-mail]

Answer 6

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

---

PMBT

Answer 7

Oops. I should have considered that possibility.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty