SQL Server – Grant All Public Users Permission to Create Tables

Well, this would be the script to restore the connection permissions to the defaults.

use [master]
GO
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Default VIA] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Local Machine] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Named Pipes] TO [public] AS [sa]

If that doesn't work, try stopping SQL Server, starting it in single user mode ("sqlservr.exe -m" from an administrator command prompt), connecting with sqlcmd.exe (which will default to the local default instance with Windows authentication), and running the script that way.

If you need to do this only once, the quickest way is probably the following.

Find all the schemas you want to touch by querying, for example, the pg_namespace system catalog:

SELECT nspname 
FROM pg_namespace
WHERE nspname LIKE 'shard%'; -- tweak this to your needs

Then we can expand the query output into GRANT statements:

SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || nspname || ' TO foo;'
FROM pg_namespace
WHERE nspname LIKE 'shard%';

This will give you an enormous amount of statements, which you can just copy over and run. As you have a big number of schemas, the easiest is possibly the following (running it in psql):

\copy (SELECT 'GRANT ...' ...) TO some_local_file.sql

\i some_local_file.sql

Of course, if you prefer, you could do this using dynamic SQL, too - I find the above solution slightly better, as it leaves a file behind with the actions taken, that can then be checked in under version control.

If you happen to use a psql version 9.6 (or later), there is just another possibility using \gexec:

SELECT 'GRANT SELECT ...'
... -- you can even omit the semicolon here 
\gexec

Notes:

• I've written an answer about a similar issue a while ago - you may find useful details there.
• there is no possibility of doing this from GRANT, unfortunately. As it is not uncommon what you need here, I'd expect this functionality appearing sooner or later.
• just to emphasize, the above commands will do the trick for the already existing tables. Future tables need the default privileges (which you've set, for a given role).