0

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?

2
1

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

1
  • tnx a lot! It seems this query lists somehow explicit permissions and not implicit/inherited ones. – Hikari Feb 10 '20 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.