Grant Delete to a Single Table

Question

Grant Delete to a Single Table

Jon-183221

SSCommitted

Points: 1908
More actions
May 1, 2009 at 9:17 am

#208774

Hi All,
I just wanted to double check something, I have a database that contains 100 tables. But for one specific user I only want to grant them delete permissions against two of the tables. Somebody suggested:
DENY DELETE TO [isc0253\iusr_hrinfo]
GRANT DELETE ON OBJECT::DBO.LOGONS TO [isc0253\iusr_hrinfo]
My feeling is that wont work as the DENY overrides a GRANT.
My guess is that I have to loop through all the tables and add deny delete against them for that user and add grants to the couple of tables I'm interested in.
Am I correct, secondly can anyone point me at some code to loop trough the tables?
Many thanks.
Jon

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

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

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

If the user does not already have delete on all the other tables then the only command you should have to execute is the grant delete on the one table.

Does that makes sense?

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Jon-183221 SSCommitted Points: 1908 More actions · Answer 2

Thanks for the input.

Sorry the account currently has db_datareader and db_datawriter roles as it needs to update\insert them.

Perphaps thats where i'm wrong I should take off db_datawriter and then just grant insert\delete onto the specific tables.

Does that make more sense?

Jon

Jon-183221 SSCommitted Points: 1908 More actions · Answer 3

Apologies I'm quite obviously being dumb, the problem is around not giving the db_datawriter role.

Jon

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 4

First, use roles. Don't grant rights to a user, even if it's one. at some point this person will move on and someone else will do their job.

Create a role, give it the rights it needs.

If you've given data writer, I believe everyone in that role has delete permissions. So create a new role, give it the rights needed. Scripting this stuff to loop over tables isn't hard.

Build a new role for this person, give them the rights they need.

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 5

If they need insert / update on all tables and only delete on the one then you could certainly leave the db_datewriter on and do the deny delete on all other tables except the one.

Hopefully that makes sense.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot