none
the delete permission was denied on the object database schema 'dbo'

    Question

  • Hi,

    user getting below error while trying to delete the data.User is a member of ad account group and this group has data_reader and data_Writer permission.

    But still they are getting this issue.

    Kindly help.

    • Moved by Tom Phillips Friday, July 13, 2018 6:08 PM Security question
    Thursday, July 12, 2018 7:27 PM

All replies

  • May be they were explicitly DENY ed DELETE permissions on the schema/table?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, July 12, 2018 7:49 PM
  • I have used this query select * from sys.database_permissions where permission_name = 'DELETE' an di can see State_desc as GRANT.I didn't find deny.

    Executed below and didn't get any output.It happend suddenly.User use to delete recently without any issues.and we don't have any changes recently.

    select * 
    from sys.database_permissions
    where major_id = object_ID('MyTableName')
    and grantee_principal_id = user_id('MyUser')

    Thursday, July 12, 2018 7:55 PM
  • Can you check if the user is mapped to 'public' role in the DB and if 'public' has explicitly been denied 'delete' permission against the said table?

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Thursday, July 12, 2018 8:25 PM
  • It has mapped to public.
    Thursday, July 12, 2018 8:28 PM
  • Can you post the exact command the user is using and the exact error message. The error message you have in the thread title is unexpected.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, July 12, 2018 8:46 PM
  • Msg:229,level 14,state ,line 1
    The delete permission was denied on the object 'table name',database 'dbname',schema dbo
    Thursday, July 12, 2018 8:50 PM
  • OK, so that is the error message I expect to see.

    Beside checking for denied permission, also check for membership in roles like db_denywriter. Keep in mind that the user may not be added directly, but through another role or AD group.

    Also, check sys.database_permissions for DENY in general. The DENY is not necessarily on the table, but could be on the schema or database level.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, July 12, 2018 9:23 PM
  • Hi Erland,

    Thanks for your support.

    Beside checking for denied permission, also check for membership in roles like db_denywriter. Keep in mind that the user may not be added directly, but through another role or AD group.

    The user is member of an AD group and that AD group has read and write access on DB.AD group is not mapped to db_denywriter.

    Also, check sys.database_permissions for DENY in general. The DENY is not necessarily on the table, but could be on the schema or database level.

    I have run the below command and didn't see any deny on delete in the result  on that db.Please let me know if there is any other to check the same.

    select * from sys.database_permissions where permission_name = 'DELETE'

    Friday, July 13, 2018 1:38 AM
  • Try this:

    SELECT * FROM sys.database_permissions WHERE state_desc = 'DENY'

    Also, do this:

    EXECUTE AS LOGIN = 'domain\thatuser'
    go
    SELECT * FROM sys.user_token
    go
    REVERT

    to verifiy that the AD group is really among the tokens.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 13, 2018 7:59 AM
  • Thanks Erland.

    I have run the script SELECT * FROM sys.database_permissions WHERE state_desc = 'DENY' and didn't fone any result.

    I executed below script and i can that AD group and it showing read,write and public permissions.

    EXECUTE AS LOGIN = 'domain\thatuser'
    go
    SELECT * FROM sys.user_token
    go
    REVERT


    Friday, July 13, 2018 5:24 PM
  • I have checked with user and user able to perform the Delete operation directly in servername123, but the issue is , user is not able to perform Delete operation on servername123 through some other servers. 

    Please advice if something we can check.

    Friday, July 13, 2018 7:28 PM
  • I have checked with user and user able to perform the Delete operation directly in servername123, but the issue is , user is not able to perform Delete operation on servername123 through some other servers. 

    Please advice if something we can check.


    As I said in the other forum, it seems that there is login-mapping set up on those servers. That is, the user maps to some other user on this server.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 13, 2018 9:44 PM