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'
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'
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:
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
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
Last activity: Nov 4, 2022, 11:57 AM