Menu
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'
223
Under Object Explorer, expand the Databases directory and then, expand the required database that contains the table. Next, expand the Tables directory and right-click the required table for which you want to check permissions, and click on the “Properties” option.
To find all the role assignments to users in SQL Server database, you can use the following query. SELECT r.name role_principal_name, m.name AS member_principal_name FROM sys. database_role_members rm JOIN sys. database_principals r ON rm.
An object-level privilege is a permission granted to an Oracle database user account or role to perform some action on a database object. These object privileges include SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX on tables and views and EXECUTE on procedures, functions, and packages.
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
Try this,
sp_helprotect "table name" go
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
