Client has Granted Update/Insert/Delete - may accomplish this with Stored Proc, but not with a direct query
Marcee last edited by
If I run
Select * from fm_my_permissions(null,'database'), the
UPDATE/INSERT/DELETEpermissions are missing from the user. The user is mapped, shows up as owner, and I ran
GRANT UPDATE/INSERT/DELETEto 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
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.
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
cross apply fn_my_permissions(c.fn,'OBJECT') p
where subentity_name = ''
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