Sample Header Ad - 728x90

script to show all the permissions for a table

12 votes
4 answers
89483 views
inside my database I have a procedure called spGen2_tblIE_SchedProcess_Update. when I run this script select * from sys.objects where name = 'spGen2_tblIE_SchedProcess_Update' enter image description here Inside the same database I have a table called tblIE_Step when I run the following script select * from sys.objects where name = 'tblIE_Step' enter image description here then I want to **see all the permissions of the objects**. when I check my stored procedure I run this script: select 'Proc' = SCHEMA_NAME(p.schema_id)+'.'+p.name , 'Type' = per.state_desc, 'Permission' = per.permission_name , 'Login' = pri.name, 'Type' = pri.type_desc , * From sys.objects as p left join sys.database_permissions as per on p.object_id = per.major_id left join sys.database_principals as pri on per.grantee_principal_id = pri.principal_id where p.object_id = 87671360 and I get the permissions: enter image description here But when I run the same for my table, I don't get anything: select 'Proc' = SCHEMA_NAME(p.schema_id)+'.'+p.name , 'Type' = per.state_desc , 'Permission' = per.permission_name , 'Login' = pri.name, 'Type' = pri.type_desc , * From sys.objects as p left join sys.database_permissions as per on p.object_id = per.major_id left join sys.database_principals as pri on per.grantee_principal_id = pri.principal_id where p.object_id = 389576426 enter image description here I know there are logins and groups that belong to roles that are have db_reader and db_writer permissions. However, they are not showing here. How can I change my script so that it would show me all the permissions for this table?
Asked by Marcello Miorelli (17274 rep)
Sep 14, 2015, 06:11 PM
Last activity: Nov 4, 2022, 11:57 AM