August 3, 2011 at 10:27 am
I have created a role and i want to add this roles to lots of tables with everything in that role set to deny. Is there a script for this
August 3, 2011 at 1:07 pm
Why DENY? Unless you have very granular or complex security requirements using DENY is not preferred.
It's much more intuitive from a management standpoint to have Users added to Roles that GRANT a specific level of access, but no more than the users in that Role require. Using this approach means you never have to use DENY.
Can you explain more about what you're trying to do?
If you must use DENY, you'll need to run something like this for each table:
DENY ALL ON [schema_name].[table_name] FROM [Role_Name]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 3, 2011 at 1:39 pm
I have a role called dyngroup that grants access to every table that uses my Great Plains Product, so I created a DenyPayroll role that Denies all but I only want it for certain users and certain tables. The DenyPayroll role will over write the Dyngroup for the specific tables I assgin that to.
I see you script, but If I have 50 payroll tables i want to add the DenyPayroll role to, I don't wan't to change the need 50 times
August 3, 2011 at 1:46 pm
Suit yourself.
I don't know the layout of your database but if you're using procs, views, functions, etc. to access your tables just be aware that DENY is not evaluated when ownership chaining is in play.
Here's how I like to handle such things:
SELECT 'before text ' + name + ' after text'
FROM sys.tables
-- WHERE clause here to return specific tables
Then I just run the output in a new query window.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply