List all permissions for a given role? has a nice query for listing users and their roles on a DB.
How could we list all tables and users with their permissions on these tables?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Give this a shot.
SELECT ST.NAME, su.name, CASE WHEN SYP.[ACTION] = 193 THEN 'SELECT' WHEN SYP.[ACTION] = 195 THEN 'INSERT' WHEN SYP.[ACTION] = 196 THEN 'DELETE' WHEN SYP.[ACTION] = 197 THEN 'UPDATE' ELSE CAST(SYP.[ACTION] AS CHAR(3)) END AS Permission FROM SYS.SYSPROTECTS SYP INNER JOIN SYS.SYSUSERS SU ON SU.uid = SYP.UID INNER JOIN SYS.SYSOBJECTS SO ON SO.id = SYP.id INNER JOIN SYS.TABLES ST ON ST.object_id = SO.id