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
-
Please elaborate on your question. To me, it is confusing about what you are asking.– SqlWorldWideCommented Feb 7, 2020 at 18:48
-
1check this: dba.stackexchange.com/questions/41234/…– CR241Commented Feb 7, 2020 at 21:31
Add a comment
|
1 Answer
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
-
tnx a lot! It seems this query lists somehow explicit permissions and not implicit/inherited ones.– HikariCommented Feb 10, 2020 at 14:49