Skip to content

Instantly share code, notes, and snippets.

@ronmichael

ronmichael/missing-permissions.sql

Created March 13, 2015 22:47
Show Gist options
  • Save ronmichael/972cc37edf9e73d18460 to your computer and use it in GitHub Desktop.
Save ronmichael/972cc37edf9e73d18460 to your computer and use it in GitHub Desktop.
Download ZIP
Identify all MSSQL database objects with no defined permissions
Raw
missing-permissions.sql
select type_desc, name
from sys.objects o
left join sys.database_permissions p on p.major_id=o.object_id
where p.class is null
and type_desc not in ('SYSTEM_TABLE','INTERNAL_TABLE','SERVICE_QUEUE','SQL_TRIGGER','TYPE_TABLE')
and type_desc not like '%CONSTRAINT%'
order by type_desc, name
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment