Client has Granted Update/Insert/Delete - may accomplish this with Stored Proc, but not with a direct query

Question

If I run Select * from fm_my_permissions(null,'database'), the UPDATE/INSERT/DELETE permissions are missing from the user. The user is mapped, shows up as owner, and I ran GRANT UPDATE/INSERT/DELETE to user, which says it ran successfully. But, that did not fix the problem.

Strangely, user may run a stored procedure with the same command, and it runs successfully. If executed directly on the table in Management Studio, it fails with the message that user does not have UPDATE permission.

I have checked for other Deny settings, particular Schema permissions, but cannot find anything. User says they have tried un-mapping and re-mapping user. It only is happening with 1 database and 2 users.

Answer 1

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