locked
assigning object level permissions to AD group doesn't filter to user? RRS feed

  • Question

  • I created a user based on an AD login and gave it db_datareader and writer.  I created a table and a user in that AD group user can select from it or insert into it.  If I add object level permissions to deny update/insert, the user can't update/insert, nor can they select from it, even though I explicitly granted select.

    I've also tried granting the permissions to a database role that the AD group is a member of, but that has the same issue.  How can I grant object level permissions to an AD group?

    Using SQL 2012.

    Monday, September 10, 2012 10:26 PM

Answers

  • I don't know what the GUI was executing behind the scenes.  Visually, I explicitly granted select and denied everything else... only that one group was on the object level permissions.  I removed all the object level permissions and did it via T-SQL and now everything is working.
    • Proposed as answer by Shulei Chen Wednesday, September 12, 2012 3:21 AM
    • Marked as answer by Shulei Chen Tuesday, September 18, 2012 10:17 AM
    Tuesday, September 11, 2012 9:14 AM

All replies

  • Least privileged role are given preference. Please make sure you have not granted DENY SELECT explicitly.   

    Kindly mark the reply as answer if they help

    Tuesday, September 11, 2012 6:32 AM
  • If you grant SELECT to the table, does it work for that user?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Tuesday, September 11, 2012 8:00 AM
  • I don't know what the GUI was executing behind the scenes.  Visually, I explicitly granted select and denied everything else... only that one group was on the object level permissions.  I removed all the object level permissions and did it via T-SQL and now everything is working.
    • Proposed as answer by Shulei Chen Wednesday, September 12, 2012 3:21 AM
    • Marked as answer by Shulei Chen Tuesday, September 18, 2012 10:17 AM
    Tuesday, September 11, 2012 9:14 AM