Remove Public and Guest Permissions

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 (13)
  1. jim says:

    Do you have a script that removes only the public permissions but leaves the guest alone?

  2. aaron says:

    What about the msdb database? Don't you need to remove them from that as well?

  3. John says:

    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.

  4. declan says:

    Thank you. Such a neat scrip. And such tidy format. Man love.

  5. Murali says:


    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








  6. Naveen Thandra says:

    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



  7. jamie says:

    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.

  8. Joshua says:

    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).

  9. jamie says:

    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

  10. kumar says:

    After running this script I still see
    GRANT EXECUTE sys sp_syspolicy_execute_policy P

  11. jamie says:

    This requirement does not appear to be in the most current STIG. I think it can safely be ignored.

  12. Bryan S says:

    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.

Comments are closed.

Skip to main content