SQL Server: Insert permission denied although permission is granted

Question

My IT-/DB-administrator and I have a problem with permissions on SQL Server.

On a number of databases I get "INSERT permission denied" although I personally have the INSERT permission granted and am a member of a group user from the AD, which also has the permission.

My permissions to SELECT, CREATE, ALTER and DELETE work fine, but the permissions to manipulate data (INSERT and UPDATE) fail although granted.

The permissions are granted on the entire DB, but we have also tried granting them on specific tables. It makes no difference.

We have tried creating a new DB and granting the same permissions. Here I can INSERT without any problems.

We have tried deleting my personal user, so that I only have the grants granted through the group, but it doesn't help.

The DB-administrator can manipulate data as can other specific users, but when the administrator tries to manipulate as member of the group he too is denied the permission.

We are running short of ideas as to what to do. Any ideas are welcome.

Answer 1

Are you sure of what permissions you have on a particular database object?

use these 2 scripts below, just to find out what permissions are actually there - and that would include any DENY too, in case you are worried about that.

in case you need more details, follow this link below:

script to show all the permissions for a table

--use MY_DATABASE
--go


SELECT
state_desc + ' ' + permission_name +
' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
     ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
     ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
where 1=1
  --AND so.name = 'my-table_name'

UNION

SELECT
state_desc + ' ' + permission_name +
' on Schema::['+ ss.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN SYS.SCHEMAS AS ss
     ON sdp.major_id = ss.SCHEMA_ID
     --AND sdp.class_desc = 'my_schema'
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
where 1=1

order by [Permissions T-SQL]
GO


--use MY_DATABASE
--go

SELECT
  (
    dp.state_desc + ' ' +
    dp.permission_name collate latin1_general_cs_as + 
    ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
    ' TO ' + '[' + dpr.name + ']'
  ) AS GRANT_STMT
FROM sys.database_permissions AS dp
  INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
  INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
  INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE 1=1
   AND dpr.name NOT IN ('public','guest')
--  AND o.name IN ('My_Procedure')      -- Uncomment to filter to specific object(s)
--AND dp.permission_name='INSERT'    -- Uncomment to filter to just the EINSERTs

Answer 2

We finally did manage to solve the problem. Our administrator thought that he had looked through all relevant users, but it turned out that he had missed a user group that had deny in the permissions. This overruled all the given permissions to edit data.

But thanks a whole lot for the script. It is likely to come in handy in future cases.