The INSERT/UPDATE/DELETE permission was denied on the object 'TheTable', database 'TheDb', schema 'dbo' I have two SQL Server user accounts on a database that behave differently on INSERT / UPDATE / DELETE. SQL Server version is 2008 R2 (10.50.4042, 64 bits).

UserA can do these commands (on any table). It is a SQL Server 'local' account (a SQL Server login, NOT a Windows Account on the local server).

DOMAIN\UserB, cannot. It comes from a Windows AD account. It is a member of Administrators group on the database server.

It get the error :


  The INSERT/UPDATE/DELETE permission was denied on the object
  'TheTable', database 'TheDb', schema 'dbo'


As far as I know, the two accounts have the same roles.
One of these roles has the permission to INSERT/UPDATE/DELETE on the tables.
The accounts are not member of the role db_denydatawriter.

I tried to grant the permission explicitly for UserB :

GRANT SELECT, UPDATE, DELETE, INSERT on TheTable TO [DOMAIN\UserB]


But I still have the error.

If I add sysadmin role on UserB, of course it has the permission, but I do not want to let it have this role.

So, to me, the only difference left is local account / Windows AD account.

Do you know a way to have more information about a potential Windows AD account specific permission?

After much more research, I suspect that UserB has a role with no permission. This Stack Overflow answer put me on the track. Here is a query showing that:

-- Users that are members of roles without any permission :
select *
from sys.database_role_members members 
left outer join sys.database_principals memberprinc on memberprinc.[principal_id] = members.[member_principal_id]
left outer join sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
where members.role_principal_id in
(
    -- Roles without any permission :
    select principal_id
    from sys.database_principals roleprinc
    where not exists 
    (
        select * 
        from sys.database_permissions perm 
        where perm.[grantee_principal_id] = roleprinc.[principal_id])
)
and memberprinc.name = 'DOMAIN\UserB'


But I am not able to edit these tables (even with sysadmin role), which is understandable.


  Msg 259 Ad hoc updates to system catalogs are not allowed.


I noticed another difference between UserA and UserB: On TheDb properties, tab Permissions, sub-tab Effective:



I found (from SQL Server Profiler) that this list comes from the following query:

EXECUTE AS LOGIN = N'DOMAIN\UserB';
SELECT 
    permission_name AS [Permission]
FROM fn_my_permissions(N'[TheDb]', N'DATABASE')
ORDER BY permission_name;
REVERT;


Do you know how to track the origin of theses permissions ?

As suggested in comments, I listed the DENY permissions for UserA and UserB:

SELECT [dp].[name] AS [user_name], [dpr].[name] AS [role_name], [per].[permission_name], [per].[state_desc], OBJECT_NAME([major_id]) AS [object_name]
FROM [sys].[database_principals] AS [dp] 
INNER JOIN [sys].[database_role_members] AS [drm] ON [dp].[principal_id] = [drm].[member_principal_id] 
INNER JOIN [sys].[database_principals] AS [dpr] ON [drm].[role_principal_id] = [dpr].[principal_id] 
INNER JOIN [sys].[database_permissions] AS [per] ON [dpr].[principal_id] = [per].[grantee_principal_id] 
WHERE [dp].[name] in ('DOMAIN\UserB', 'UserA') AND [per].[state_desc] = 'DENY'
ORDER BY [dp].[name];


The two users have the very same DENY permissions on two objects:


user_name   role_name   permission_name state_desc  object_name
---------------------------------------------------------------
UserA        RoleC      REFERENCES      DENY        ObjectD
UserA        RoleC      REFERENCES      DENY        ObjectE
DOMAIN\UserB RoleC      REFERENCES      DENY        ObjectD
DOMAIN\UserB RoleC      REFERENCES      DENY        ObjectE


Does anyone have some more clues?

SELECT SUSER_NAME() does return DOMAIN\UserB when I log in as DOMAIN\UserB.
Try with the below script. Its works for me.

GRANT SELECT ,UPDATE,DELETE,INSERT ON OBJECT::YourTable  
    TO [Domain\User];  
GO   


I tried to the mentioned permission to a user Anoop,in the database MyLab.

GRANT SELECT ,UPDATE,DELETE,INSERT ON OBJECT::Account  
    TO [*MyDomain*\anoop];  
GO   


And below is the screen shot after giving the permission.