removing Public role from SQL server

For some reason my post from th morning is gone.

I am trying to look into a solution whereby I can avoid giving public role access to all the database table in SQL sever.
I would like to work with a security model where only the table owner ( Proddta etc) or proxy user (PSFT) have access to table. and I would remove the security from Public to these tables.

Has any one done this . What were the downsides of this approach. Does any one have any other suggetion to achive this.


Appeicate your comments and helpful suggestion



We are e810 on Sql with Win2003 app servers

Hi,

Yes, it's possible.
Let's do an example with JDE_CRP.
First, GRANT PSFT, CRPDTA and CRPCTL full access to
the database JDE_CRP and to all of its tables.
Second, you remove access to JDE_CRP to logins other than
PSFT, CRPDTA and CRPCTL.
Finally, you DENY all rights to PUBLIC on every
table of JDE_CRP.

We have successfully done this. We placed the table owners and JDE accounts into the database roles db_datareader, db_datawriter, and db_ddladmin and then revoked all from the public role (via a script). The only issue afterward is that JDE continues to grant permissions to the public role when you generate a table. You have to go in after each table generation and remove the permissions - we've just made it part of our process to do so and we also have a script that we run daily to verify that there are no inappropriate permissions. If you're interested, send me a private message with your e-mail and I can provide you with the scripts we use.