Y
To examine the effective permissions on an object for a user, connect as or impersonate the user and run
with c as
(
SELECT concat(quotename(schema_name(schema_id)),'.', quotename(name)) fn
from sys.objects o
where o.is_ms_shipped = 0
)
Select *
from c
cross apply fn_my_permissions(c.fn,'OBJECT') p
where subentity_name = ''
Note that there is an edge case that will cause an object not to appear in this view, which is when the user running the query lacks VIEW DEFINITION on the target object, which you can work around by listing the objects before impersonating the target user, eg
DECLARE @objects table (fn nvarchar(255))
insert into @objects(fn)
SELECT concat(quotename(schema_name(schema_id)),'.', quotename(name)) fn
from sys.objects o
where o.is_ms_shipped = 0
execute as user='someuser';
with c as
(
SELECT * FROM @objects
)
Select *
from c
cross apply fn_my_permissions(c.fn,'OBJECT') p
where subentity_name = ''
revert
And you can examine all the object, schema, and database grants like this:
select case when class_desc = 'OBJECT_OR_COLUMN' then schema_name(o.schema_id) + '.' + o.name
when class_desc = 'DATABASE' then 'DATABASE'
when class_desc = 'SCHEMA' then 'schema::' + schema_name(p.major_id)
end name,
o.type_desc,
u.name grantee,
p.class_desc,
p.permission_name,
p.state_desc
from sys.database_permissions P
join sys.database_principals u
on u.principal_id = p.grantee_principal_id
left join sys.objects o
on p.major_id = o.object_id
where class_desc in( 'OBJECT_OR_COLUMN', 'DATABASE', 'SCHEMA')
and p.major_id >= 0
