Rabbi Rabbi - 6 months ago 14
SQL Question

list of users and roles that have permissions to an object (table) in SQL

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:

SELECT role_name
FROM permissions
where object_name = 'the_table_i_need_to_know_about'

Answer

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