Jaa

Remove Public and Guest Permissions

  • Artikkeli

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:

  • Revokes the VIEW ANY DATABASE permission from the master database. (server-level)
  • Revokes the CONNECT permissions from the guest accounts on all databases except master and tempdb to disable those guest accounts. (server-level) It is not possible to remove the CONNECT permissions from the guest accounts on the master or tempdb databases nor can they be disabled by any other means.
  • Revokes all object privileges assigned to public or guest for every database. (database-level)

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!

Remove Public and Guest Permissions, v3.sql

Comments

  • Anonymous
    November 03, 2011
    Do you have a script that removes only the public permissions but leaves the guest alone?

  • Anonymous
    March 05, 2012
    What about the msdb database? Don't you need to remove them from that as well?

  • Anonymous
    July 31, 2012
    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.

  • Anonymous
    November 23, 2012
    Thank you. Such a neat scrip. And such tidy format. Man love.

  • Anonymous
    June 13, 2013
    Hi, 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. use Master go DENY VIEW ANY DEFINITION TO public; GO DENY VIEW ANY DATABASE TO public; GO Regards Murali

  • Anonymous
    August 05, 2013
    Hello John I have executed your script, but I faced few issues in application connectivity, Can you please send me/post the roll back script navee.don@gmail.com Thanks, Naveen

  • Anonymous
    September 19, 2013
    Issue resolved by referring the below link. naveenthandra.blogspot.in/.../how-to-reset-permission-for-public-role.html

  • Anonymous
    January 24, 2014
    The comment has been removed

  • Anonymous
    April 08, 2014
    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).

  • Anonymous
    May 19, 2014
    Joshua,
    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

  • Anonymous
    March 16, 2015
    The comment has been removed

  • Anonymous
    August 03, 2015
    This requirement does not appear to be in the most current STIG. I think it can safely be ignored.

  • Anonymous
    October 08, 2015
    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.