CODEWITHSUNDEEP
sql-server
Tarzan
Tarzan

Reputation: 4538

How can I make a SQL Server schema accessible to only one role?

I have a SQL Server database with a schema called [TopSecret]. I have a database role called [CLevel].

How can I restrict [TopSecret] permissions to only members of the [CLevel] role?

Note: sysadmin and database owners may see the [TopSecret] schema and this is OK, but no other user or role should

Upvotes: 0

Views: 776

Answers (1)

Randy in Marin
Randy in Marin

Reputation: 1143

The normal method would be to only grant access to the CLevel schema only to the CLevel role. This would require not granting anybody else access to the objects in the schema. This means not using database roles like db_owner, db_datareader, etc. This takes discipline.

A more forceful method is to use deny. One possible method is to create two roles with mutually exclusive membership, perhaps [CLevel] and [NotCLevel]. The deny will have priority if a user is in both groups. NotCLevel must have all users that have access to the database but are not in CLevel. Using a database level role access will not automatically grant access to CLevel because deny takes priority. Use something like the following for all schema permissions possible.

GRANT SELECT ON SCHEMA :: CLevel TO Clevel
DENY SELECT ON SCHEMA :: CLevel TO NotClevel

DENY has no effect on the database owner or members of sysadmin because permission checks are skipped. Otherwise, a deny once issued could never be revoked. The deny does work on members of db_owner if they are not the owner or a member of sysadmin.

It might be hard to manage. A login trigger perhaps can automatically add users to the NotCLevel role if not in either NotCLevel and CLevel.

https://learn.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-ver16

Upvotes: 2

Related Questions