locked
Deny Update Errow 229, 14, 5 RRS feed

  • Question

  • Hello and thanks for your time. I have a strange denial from SQL Server. User has login using Windows network credentials (Domain\username) and his Name/LoginName are the same. He has access to database XDB, and was granted db_owner in this database. Too be certain, we dropped him from the database and then dropped his login and then added his login and mapped him to the XDB with db_owner permissions. His user id in the XDB database is 13. Yet when he attempts to update a particular table, he get's the 229 error as follows ...

    The UPDATE permission was denied on the object 'STORE_TRANSFER_MATRIX', database 'XDB', schema 'dbo'.

    Running

    select

    user_name(grantee), * from sys.syspermissions where grantee = 13

    returns

    Domain\UserName 0 13 1 NULL NULL NULL NULL NULL NULL NULL NULL

    Running

    select

     

    * from

    sys.database_permissions

     

    where grantee_principal_id = 13

    returns

    0 DATABASE 0 0 13 1 CO CONNECT G GRANT

    Running

    select

     

    dp.NAME AS principal_name

    ,

    dp

    .type_desc AS principal_type_desc

    ,

    o

    .NAME AS object_name

    ,

    p

    .permission_name

    ,

    p

    .state_desc AS

    permission_state_desc

     

    from sys.database_permissions

    p

     

    left OUTER JOIN sys.all_objects o on p.major_id = o.

    OBJECT_ID

     

    inner JOIN sys.database_principals dp on p.grantee_principal_id = dp.

    principal_id

     

    where o.name = 'STORE_TRANSFER_MATRIX'Returns nothing!!!


    Andrew Smith, SQL Server DBA
    Wednesday, January 11, 2012 10:25 PM

Answers

  • No, but there is no UPDATE, DELETE or INSERT. And these permission is implied by CONTROL. To confirm this, perform the same exercise, now with EXECUTE AS USER = 'dbo' (Or just run as yourself, if you are sysadmin.)

    So you will need to look for DENY permissions in sys.database_permissions. And don't forget to check whether db_denydbwriter has any members.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Andrew_Smith Thursday, January 12, 2012 4:47 PM
    Thursday, January 12, 2012 8:52 AM
  • Erland:

    Thanks again for your assistance. He was in the db_denydatawriter fixed database role. I thought that a 'deny' record would show up in the fn or dmv but I guess not. This is the first time I have encountered this particular issue.

    Regards,


    Andrew Smith, SQL Server DBA
    • Marked as answer by Andrew_Smith Thursday, January 12, 2012 4:46 PM
    Thursday, January 12, 2012 4:46 PM

All replies

  • This is a SQL 9.0.5000 instance.


    Andrew Smith, SQL Server DBA
    Wednesday, January 11, 2012 10:29 PM
  • Could he be member of a Windows group that has been denied access (with the DENY command)?

    A good way to check the permissions for a user is to run:

    EXECUTE AS LOGIN = 'Domain\User'
    go
    SELECT * FROM fn_my_permissions(par1, par2)
    go
    REVERT

    Check Books Online for the arguments to fn_my_permissions.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 11, 2012 11:00 PM
  • Erland:

    Thanks for the reply. I ran the code you suggested and this is what I received from the function call for the table in question.

    entity_name subentity_name permission_name
    STORE_TRANSFER_MATRIX  SELECT
    STORE_TRANSFER_MATRIX  REFERENCES
    STORE_TRANSFER_MATRIX  VIEW DEFINITION
    STORE_TRANSFER_MATRIX  ALTER
    STORE_TRANSFER_MATRIX  TAKE OWNERSHIP
    STORE_TRANSFER_MATRIX  CONTROL
    STORE_TRANSFER_MATRIX STORE_ID SELECT
    STORE_TRANSFER_MATRIX TRANSFER_ID SELECT
    STORE_TRANSFER_MATRIX STORE_TRANSFER_ID SELECT
    STORE_TRANSFER_MATRIX RECORD_STATUS SELECT
    STORE_TRANSFER_MATRIX RECORD_STATUS_DATE SELECT
    STORE_TRANSFER_MATRIX STORE_ID REFERENCES
    STORE_TRANSFER_MATRIX TRANSFER_ID REFERENCES
    STORE_TRANSFER_MATRIX STORE_TRANSFER_ID REFERENCES
    STORE_TRANSFER_MATRIX RECORD_STATUS REFERENCES
    STORE_TRANSFER_MATRIX RECORD_STATUS_DATE REFERENCES

    I do not see any "DENY" records.


    Andrew Smith, SQL Server DBA
    Wednesday, January 11, 2012 11:49 PM
  • No, but there is no UPDATE, DELETE or INSERT. And these permission is implied by CONTROL. To confirm this, perform the same exercise, now with EXECUTE AS USER = 'dbo' (Or just run as yourself, if you are sysadmin.)

    So you will need to look for DENY permissions in sys.database_permissions. And don't forget to check whether db_denydbwriter has any members.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Andrew_Smith Thursday, January 12, 2012 4:47 PM
    Thursday, January 12, 2012 8:52 AM
  • Erland:

    Thanks again for your assistance. He was in the db_denydatawriter fixed database role. I thought that a 'deny' record would show up in the fn or dmv but I guess not. This is the first time I have encountered this particular issue.

    Regards,


    Andrew Smith, SQL Server DBA
    • Marked as answer by Andrew_Smith Thursday, January 12, 2012 4:46 PM
    Thursday, January 12, 2012 4:46 PM
  • I would execpt an explicit "DENY UPDATE ON tbl" to show up in sys.database_permissions. (Not in fn_my_permissions, though.)

    But the role membership is a different story.

    Yes, there are a bit too many knobs in this area. But generally, avoid DENY and the deny roles if you can. Since default is "no permission", they are only needed to override GRANT that are too far-reaching.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 12, 2012 10:47 PM