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