Deny access in general EXCEPT for a few tables?

I've tried to use the db_denydatawriter role to make sure the default rule is to deny and then added some explicit update/delete privileges to specific tables and the deny is still enforced. I could instead not use db_denydatawriter and set individual table permissions, but I was trying to avoid new tables being added and people forgetting to apply deny write permissions to them. Any ideas?

more ▼

asked Mar 25, 2010 at 11:08 AM in Default

avatar image

28 2 3 6

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Deny takes precedence over grant, so instead of denying access to everything, you should only grant access to the stuff that you want them to have access to.

more ▼

answered Mar 25, 2010 at 11:16 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

The key point to remember is that if you have not been assigned write permissions, then you do not have them and cannot write to that table. The explicit deny permission is mostly there to override inherited permissions from a group. Other than that case, you would very rarely need them.

Mar 25, 2010 at 11:55 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 25, 2010 at 11:08 AM

Seen: 2335 times

Last Updated: Mar 25, 2010 at 11:08 AM

Copyright 2018 Redgate Software. Privacy Policy