SQL Server – Script to Show All Permissions for a Table

automationloginspermissionsscriptingsql server

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?

Best Answer

I've got a couple of stored procedures you can use to display all of the permissions for a given database. Either for a single user/principal or for all of them. sp_dbpermissions and sp_srvpermissions.

The output for sp_dbpermissions (sp_srvpermissions is the same at a server level) looks like this

enter image description here

Off the page are revoke/grant/deny commands as appropriate.

FYI the example I have here was run using 'ALL' for the database parameter so it's displaying the output for all of the databases not just one.

I primarily intended them as research tools so you will find parameters that let you search out permissions directly applied to a given object (similar to your query above) or members of a given role (and the permissions for that role) etc.

Note: Minion also has an enterprise edition that does a lot of detailed data collection on permissions that might very well give you what you want. It is however a paid program. (If you can talk your management into the expense it's probably worth it)

Related Question