October 3, 2003 at 2:06 am
Hi!
I would like to make the public role in a database always include the permissions from the db_denydatareader and db_denydatawriter roles.
Would a public role with the following added permissions be equivivalent to "public + db_denydatareader + db_denydatawriter":
DENY ALL ON table1 TO public
..
DENY ALL ON tableN TO public
DENY ALL ON storedproc1 TO public
..
DENY ALL ON storedprocN TO public
..
and so on for all objects (views, udfs ..)
Brgds
Jonas
BrgdsJonas
October 3, 2003 at 5:06 am
Set it up that way in model and when you create a new DB (since it is based on model) it will be tht way in the new DB. As for the added permissions I believe you will need to create a script for those.
October 3, 2003 at 5:14 am
Yes, I guess I can use sp_MSforeachtable for the user tables and then hardcoded statements for the system tables.
Thanks for your answer
Jonas
BrgdsJonas
October 3, 2003 at 5:16 am
Why would you want to do it though?
Andy
October 3, 2003 at 5:19 am
I want to restrict the access to all tables (and the rest of the objects possibly) in the database, leaving only a layer of stored procedures that can be executed. I read somewhere that a recommendation was to use the db_denydatareader & db_denydatawriter roles, but they can only be applied to users, not other roles.
Jonas
BrgdsJonas
October 3, 2003 at 6:03 am
Sorry, I understand now. Actually, create your own role and place your users in that role. You can then make a user-defined role a memeber of another role so add to db_denydatareader and db_denydata writer. Then you script that process for all users which will do the job.
October 3, 2003 at 6:19 am
Great idea! That way I don't have to deal with the individual tables because db_denydatareader/writer takes care of that.
Thanks
Jonas
BrgdsJonas
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply