9

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 :

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 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:

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

2 Answers 2

0

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
  • 2
    Thanks for your answer. Can you give some sample code ? Jan 14, 2020 at 13:44
-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
  • 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

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