I have a table of metadata, listing every variable in every table in my database, which when a variable is flagged as 'Released' it triggers a stored procedure to deny UPDATE permissions on that field in its respective table, and also deny INSERT and DELETE permissions on the table as a whole, for 3 specific Users everytime.

It works perfectly! But I'd like some kind of checking method that I can run every so often to confirm that all released variables have had their UPDATE permissions removed for these 3 Users - so I can check no pesky admins have gone in and changed them sometime!

Is there a way I can see the fields in every table in a database, along with their UPDATE permissions for each User, so then I can compare it to my metadata table to confirm Released columns have indeed had their UPDATE permissions altered?

I am using SSMS v17.9.

  • Why not remove permissions from "pesky admins" if they've no rights to be doing this? – Vérace Jan 14 at 14:26
  • @Vérace It's also just to keep ensuring the triggers and procedures haven't failed at some point. – al_sweets Jan 14 at 15:04
  • How does one put "variables" in tables? Do you mean columns? – Michael Green Jan 15 at 11:04
  • @MichaelGreen Of course. – al_sweets Jan 15 at 15:02

All this information is the the catalog views. Here's a query to get you started:

select schema_name(t.schema_id) schema_name, 
       t.name table_name, 
       c.name column_name, 
from sys.tables t
join sys.columns c
  on t.object_id = c.object_id
join sys.database_permissions p
  on c.object_id = p.major_id
 and c.column_id = p.minor_id
where p.class_desc = 'OBJECT_OR_COLUMN'

Not the answer you're looking for? Browse other questions tagged or ask your own question.