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