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

sql tsql permissions azure-sql-database
Rabbi picture Rabbi · May 12, 2011 · Viewed 65.1k times · Source

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

Herve Roggero picture Herve Roggero · May 16, 2011

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

Related questions

GRANT EXECUTE to all stored procedures

Does the following command effectively give the user, "MyUser," permission to execute ALL stored procedures in the database? GRANT EXECUTE TO [MyDomain\MyUser]

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More