How to find permissions granted on individual objects in a database?

Run the following SQL to find permissions granted on individual objects on the database

select
case when p.protecttype = 205 then 'GRANT'
when p.protecttype = 206 then 'DENY'
else 'unknown'
end + ' ' +
case when p.action = 193 then 'SELECT'
when p.action = 197 then 'UPDATE'
when p.action = 195 then 'INSERT'
when p.action = 196 then 'DELETE'
when p.action = 224 then 'EXECUTE'
else 'unknown'
end + ' ON ' + objectowner.name + '.' + o.NAME + ' TO ' + u.name
from sysusers u
inner join sysprotects p on u.uid = p.uid
inner join sysobjects o on o.id = p.id and o.name = 'object_name'
inner join sysusers objectowner on o.uid = objectowner.uid
order by o.name, u.name

Leave a Reply