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

    Pertanyaan

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

    • Dipindahkan oleh Tom Phillips Jumat, 13 Juli 2018 18.08 Security question
    Kamis, 12 Juli 2018 19.27

Semua Balasan

  • 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

    Kamis, 12 Juli 2018 19.49
  • 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')

    Kamis, 12 Juli 2018 19.55
  • 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.

    Kamis, 12 Juli 2018 20.25
  • It has mapped to public.
    Kamis, 12 Juli 2018 20.28
  • 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

    Kamis, 12 Juli 2018 20.46
  • Msg:229,level 14,state ,line 1
    The delete permission was denied on the object 'table name',database 'dbname',schema dbo
    Kamis, 12 Juli 2018 20.50
  • 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

    Kamis, 12 Juli 2018 21.23
  • 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'

    Jumat, 13 Juli 2018 01.38
  • 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

    Jumat, 13 Juli 2018 07.59
  • 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


    Jumat, 13 Juli 2018 17.24
  • 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.

    Jumat, 13 Juli 2018 19.28
  • 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

    Jumat, 13 Juli 2018 21.44