WIldcards for SQL schemas

No, you cannot use wildcards. However you could write a simple script to generate the commands and run them.

select 'REVOKE ' + prm.permission_name + ' ON ' + CONCAT(OBJECT_SCHEMA_NAME(major_id), CASE WHEN OBJECT_SCHEMA_NAME(major_id) IS NULL THEN '' ELSE '.' END, OBJECT_NAME(major_id)) + ' TO ' + rol.name + char(13) COLLATE Latin1_General_CI_AS
from sys.database_permissions prm
join sys.database_principals rol on
prm.grantee_principal_id = rol.principal_id
where rol.name = 'public'

Basic usage:

REVOKE [ GRANT OPTION FOR ] permission [ ,...n ]  
     ON SCHEMA :: schema_name  
     { TO | FROM } database_principal [ ,...n ]  
     [CASCADE]  
     [ AS revoking_principal ]  

Tom's solution works, you can try running it.

Bert Zhou

As Tom says, this is not a common operation, so there is no single command to do this. However, as Tom showed you, it is very easy to generate a list of commands. Then you copy from the results grid into a query window to execute the lot. It is also possible to write a script that receives the command in a variable and then executes it directly. If I need to something in a single database, I don't care about doing this, but I do a copy-paste. But if you need to do this in many databases, yes, you don't want to copy and paste for every database. I have written about this technique from a general perspective here: https://www.sommarskog.se/dynamic_sql.html#alldatabases.

However, when I read your original post more closely, I get a little nervous when you say:

Syntax goes like this: REVOKE SELECT ON [sys]. [name] TO public AS [dbo].

Are you looking into removing SELECT permission for public on objects in the sys schema? I strongly recommend against this. Very likely, this will break things. Generally, the catalog views, dynamic management views etc perform their own permission check, so users can only retrieve information they have permission to. But if you revoke SELECT permissions on, say, sys.objects, APIs that retrieves metadata under the cover, will stop functioning.