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 (SQL Login): 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).

UserB (Windows Login): 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 members of the role db_denydatawriter.

I tried to grant the permission explicitly for 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 vs. 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:

Effective permissions

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

    permission_name AS [Permission]
FROM fn_my_permissions(N'[TheDb]', N'DATABASE')
ORDER BY permission_name;

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.


2 Answers 2


You have to add the object as a securable to the user, in the target database and then add the securable necessary and give update permission.

  • 1
    Welcome to Database Administrators. Please, check How do I write a good answer? to better contribute with your knowledge.
    – Ronaldo
    Jan 13, 2020 at 20:34
  • 1
    Thanks for your answer. Can you give some sample code ? Jan 14, 2020 at 13:44

Try with the below script. Its works for me.

    TO [Domain\User];  

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

    TO [*MyDomain*\anoop];  

And below is the screen shot after giving the permission.

enter image description here

  • 4
    Thanks for your answer. You're right, this command should have worked, like I stated in my OP. but it did not, for a reason I don't know... Sep 5, 2016 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.