SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


table security


table security

Author
Message
mstephens1754
mstephens1754
SSC-Addicted
SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)

Group: General Forum Members
Points: 485 Visits: 165
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.
SQLAssAS
SQLAssAS
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2796 Visits: 1297
use [database]
Grant PERMISSION on [tablename] to [user]
Elliott Whitlow
Elliott Whitlow
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38664 Visits: 5314
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: Administrators
Points: 221339 Visits: 19617
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
My Blog: www.voiceofthedba.com
mstephens1754
mstephens1754
SSC-Addicted
SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)

Group: General Forum Members
Points: 485 Visits: 165
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?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: Administrators
Points: 221339 Visits: 19617
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search