5

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:

Effective permissions

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.

-1

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.

enter image description here

  • 1
    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... – Dude Pascalou Sep 5 '16 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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