Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

table security Expand / Collapse
Author
Message
Posted Friday, December 13, 2013 4:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:50 PM
Points: 43, Visits: 142
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.
Post #1522629
Posted Friday, December 13, 2013 7:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:17 AM
Points: 264, Visits: 800
use [database]
Grant PERMISSION on [tablename] to [user]
Post #1522686
Posted Friday, December 13, 2013 9:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #1522762
Posted Friday, December 13, 2013 10:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:39 PM
Points: 33,155, Visits: 15,291
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).







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1522793
Posted Monday, December 16, 2013 4:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:50 PM
Points: 43, Visits: 142
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?
Post #1523147
Posted Monday, December 16, 2013 8:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:39 PM
Points: 33,155, Visits: 15,291
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1523256
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse