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 firstname.lastname@example.org
Issue resolved by referring the below link.
You list the ** 3. Change the execution mode by uncommenting: Set @modeScriptOnly = 0; but it is uncommented in the download. Is this the intention? If so, it is confusing. I would think that by default, when the script is downloaded, that it would be best not to leave it set to execute the generated commands. 3. is misleading.
John - A couple of notes - First, I've used this script a number of times on both SQL 2005 and 2008 servers to make them STIG compliant (with success), but have run into a number of occasions where functions in the application using SQL server was in fact dependent on having those public permissions in place. Just something for people to keep in mind - make sure you test everything thoroughly before rolling into production, and keep a copy of an "undo" script to roll back the permission changes.Second, apparently, the removal of the public role permissions is not required under the SQL 2012 STIG (I suspect due to the reason above, where too many applications are designed to require use of those permissions).
The STIG says the user 'public' not the role 'public' must be removed. I mis-read it the first time.
That's a big distinction.
Rule Title: SQL Server default account public must be removed from each database.
STIG ID: SQL2-00-023400 Rule ID: SV-53924r1_rule Vuln ID: V-41398
Severity: CAT II Class: Unclass
After running this script I still see
GRANT EXECUTE sys sp_syspolicy_execute_policy P
This requirement does not appear to be in the most current STIG. I think it can safely be ignored.
There is a bug in the SELECT statement in csrObjects. You must JOIN to @databases.sys.schemas for the schema name. The SCHEMA_NAME() function outputs the schema names from the database you're running the query in, not the @database you're scripting for.