Skip to content

Instantly share code, notes, and snippets.

@taniomi

taniomi/grant_select_on_all_schemas_except.sql

Created March 11, 2025 13:26
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save taniomi/3723025ed1068b9348a59c07b66fb663 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save taniomi/3723025ed1068b9348a59c07b66fb663 to your computer and use it in GitHub Desktop.
Download ZIP
Grants select on all schemas except for specified ones
Raw
grant_select_on_all_schemas_except.sql
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql +=
'GRANT SELECT ON SCHEMA::' + QUOTENAME(name) + ' TO [role];' + CHAR(13)
FROM sys.schemas
WHERE name NOT IN ('dbo', 'dev')
AND name NOT LIKE 'db_%'; -- Exclude schemas starting with "db_"
PRINT @sql; -- Check generated SQL before executing
EXEC sp_executesql @sql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment