You can't get rid of the "public" role and by default in SQL Server 2005 and 2008 many objects have permissions granted to public. For those reasons, you might expect that those permissions are required for SQL Server to function correctly, but you'd be wrong. In fact, you have to wipe them out to comply with the DoD Database STIG version 8 requirements DG0080 and DG0119. For SQL Server, the specific SRR's requirements are DM6196 and DM1709 (VKeys V0015172 and V0015171).
Since there're way too many objects to remove permissions one-by-one, I've written a script you can use to remove them, and it's attached to this post. The script does the following:
The script covers the model database, so any databases you create after running the script should be compliant with DM6196 and DM1709.
WARNING: Before you remove these permissions on a production server, you should test the effects on a non-production system and resolve any resulting problems. The most likely problem (and the only one I have seen so far) will be loss of connectivity by accounts that are not members of SQL Server's sysadmin fixed server role and do not have specifically granted connection permissions. By default, non-admin accounts get their permissions to connect by inheriting CONNECT SQL permissions from the guest account and by the default VIEW ANY DATABASE permission the public role has. For more information on this issue, see this post.
Revisions: This entire post was revised on 12Aug2011.
Script revised by Rick Davis, Microsoft Senior Consultant, to allow it to work with object names with characters such as hyphens and spaces, and a new script-only mode. Run it as-is and it will execute the revoke commands, but you can change the @modeScriptOnly variable to "1" at the beginning of the script and it will only print the commands. Great job, Rick!
Do you have a script that removes only the public permissions but leaves the guest alone?
What about the msdb database? Don't you need to remove them from that as well?
Great article specifically refresing DoD STIGS. Script works great on SQL 2012 as I am in the process of applying STIGS to SQL 2012 now.
Thank you. Such a neat scrip. And such tidy format. Man love.
Thanks for the script. wondering can the below lines substitute the above code. It seems to be working for me. am I missing something at my end.
DENY VIEW ANY DEFINITION TO public;
DENY VIEW ANY DATABASE TO public;
I have executed your script, but I faced few issues in application connectivity, Can you please send me/post the roll back script email@example.com
Issue resolved by referring the below link.