User can select, but not update. Both are granted. Ideas?

  • So there's this user... (does kind of start out like a joke, doesn't it?)

    I added the user's AD ID to SQL as a login.

    I added the login as a user to the database.

    I added the user to the proper role.

    I granted the role SELECT, INSERT, UPDATE, DELETE, REFERENCES to a table.

    The user can select from the table, but not update it. The column the user is attempting to update does not reference / is not referenced by a FK.

    So what did I do wrong? 🙂

    Thanks,

    -David.

  • what error is thrown? if the update completes but the change isn't there after, is there a trigger on the table?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • What other login paths does this user have? Use: xp_logininfo 'login', 'all'

    Does he have any explicit denies against any of his logins that give hime access?


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Are there any Windows Roles that have access to the Database? Is that user also in one of the roles with an explicit deny?

  • @TheSQLGuru - The exact error is:

    Msg 229, Level 14, State 5, Line 1

    The UPDATE permission was denied on the object 'Levels', database 'hqWComp', schema 'dbo'.

    @robert Davis / @jack-2 Corbett - You are correct. The user is in at least two other groups that are in the role db_denydatawriter. I'll have to see what else I can do for this person.

    Thanks for your help, everyone!

    -David.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply