table security

  • Guys I would like to know can you grant table security in sql 2008? If it can be done can you do it based on active directory user group I normally do security at the database level however a business unit has requested table level security.

  • use [database]

    Grant PERMISSION on [tablename] to

  • You should push back to the business unit, table level security should be the EXCEPTION and rare. In short they can have it but just for a few tables and with a VERY good explanation why. Often times they don't understand what they are asking for and can't articulate why they "need" it. You are the keeper of the SQL box, you need to make sure they don't do stupid things without a REALLY good reason..

    CEWII

  • Use multiple roles. I agree with Elliot this should be justified because it's a PIA for administration, but I'd just create new roles, perhaps one for each table if needed, and grant rights to the roles, dropping people in those roles.

    To simplify, I'd actually use AD groups and stick multiple roles (tables) with an AD Group (AD Group has multiple roles). Then the "table" security management is handled by the security group that puts people into the proper roles (or removes them).

  • I ended up creating a role I than added the table objects to the role and mapped an AD group to the role. However, I was wondering is there a way to copy a role than rename it that way I could knock out the other roles quickly basically go back in and tweak only the table I need for that role?

  • Script out the role. You should do that anyway.

    After you create it in the GUI, don't click "OK", click the "script button" at the top, save that script as a record of what you did, and copy it to build other similar scripts.

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

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