You'd think I'd be able to Google such a simple question. But no matter what I try, I hit a brick wall.
What is the TSQL statement to find a list of roles that have permissions to a table?
The pseudo-code looks like this:
where object_name = 'the_table_i_need_to_know_about'
It's a bit tricky. First, remember that the built-in roles have pre-defined access; these won't show up in the query below. The proposed query lists custom database roles and which access they were specifically granted or denied. Is this what you were looking for?
select permission_name, state_desc, type_desc, U.name, OBJECT_NAME(major_id) from sys.database_permissions P JOIN sys.tables T ON P.major_id = T.object_id JOIN sysusers U ON U.uid = P.grantee_principal_id