Skip to main content
Tweeted twitter.com/StackDBAs/status/1267923968343883779
edited tags
Link
Marcello Miorelli
  • 16.6k
  • 53
  • 170
  • 311
edited tags
Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663
Source Link
Marcello Miorelli
  • 16.6k
  • 53
  • 170
  • 311

script to show all the permissions for a table

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?