Remove Public and Guest Permissions

Published Mar 23 2019 11:25 AM 2,818 Views
Microsoft
First published on TECHNET on Feb 04, 2010

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

%3CLINGO-SUB%20id%3D%22lingo-sub-383594%22%20slang%3D%22en-US%22%3ERemove%20Public%20and%20Guest%20Permissions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383594%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20TECHNET%20on%20Feb%2004%2C%202010%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EYou%20can't%20get%20rid%20of%20the%20%22public%22%20role%20and%20by%20default%20in%20SQL%20Server%202005%20and%202008%20many%20objects%20have%20permissions%20granted%20to%20public.%20For%20those%20reasons%2C%20you%20might%20expect%20that%20those%20permissions%20are%20required%20for%20SQL%20Server%20to%20function%20correctly%2C%20but%20you'd%20be%20wrong.%20In%20fact%2C%20you%20have%20to%20wipe%20them%20out%20to%20comply%20with%20the%20DoD%20Database%20STIG%20version%208%20requirements%20DG0080%20and%20DG0119.%20For%20SQL%20Server%2C%20the%20specific%20SRR's%20requirements%20are%20DM6196%20and%20DM1709%20(VKeys%20V0015172%20and%20V0015171).%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESince%20there're%20way%20too%20many%20objects%20to%20remove%20permissions%20one-by-one%2C%20I've%20written%20a%20script%20you%20can%20use%20to%20remove%20them%2C%20and%20it's%20attached%20to%20this%20post.%20The%20script%20does%20the%20following%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3ERevokes%20the%20VIEW%20ANY%20DATABASE%20permission%20from%20the%20master%20database.%20(server-level)%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ERevokes%20the%20CONNECT%20permissions%20from%20the%20guest%20accounts%20on%20all%20databases%20except%20master%20and%20tempdb%20to%20disable%20those%20guest%20accounts.%26nbsp%3B(server-level)%20It%20is%20not%20possible%20to%20remove%20the%20CONNECT%20permissions%20from%20the%20guest%20accounts%20on%20the%20master%20or%20tempdb%20databases%20nor%20can%20they%20be%20disabled%20by%20any%20other%20means.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ERevokes%20all%20object%20privileges%20assigned%20to%20public%20or%20guest%20for%20every%20database.%20(database-level)%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EThe%20script%20covers%20the%20model%20database%2C%20so%20any%20databases%20you%20create%20after%20running%20the%20script%20should%20be%20compliant%20with%20DM6196%20and%20DM1709.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3EWARNING%3A%20%3C%2FSTRONG%3E%20Before%20you%20remove%20these%20permissions%20on%20a%20production%20server%2C%20you%20should%20test%26nbsp%3Bthe%26nbsp%3Beffects%26nbsp%3Bon%20a%20non-production%20system%20and%20resolve%20any%20resulting%20problems.%20The%20most%20likely%20problem%20(and%20the%20only%20one%20I%20have%20seen%20so%20far)%20will%20be%20loss%20of%20connectivity%20by%20accounts%20that%20are%20not%20members%20of%20SQL%20Server's%20sysadmin%20fixed%20server%20role%20and%20do%20not%20have%20specifically%20granted%20connection%20permissions.%20By%20default%2C%20non-admin%20accounts%20get%20their%20permissions%20to%20connect%20by%20inheriting%20CONNECT%20SQL%20permissions%20from%20the%20guest%20account%20and%20by%20the%20default%20VIEW%20ANY%20DATABASE%20permission%20the%20public%20role%20has.%20For%20more%20information%20on%20this%20issue%2C%20see%20%3CA%20href%3D%22http%3A%2F%2Fblogs.technet.com%2Fb%2Ffort_sql%2Farchive%2F2010%2F07%2F06%2Fconnection-error-after-removing-public-permissions.aspx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20this%20post%20%3C%2FA%3E%20.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERevisions%3A%20This%20entire%20post%20was%20revised%20on%2012Aug2011.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EScript%20revised%20by%20Rick%20Davis%2C%20Microsoft%20Senior%20Consultant%2C%20to%20allow%20it%20to%20work%20with%20object%20names%20with%20characters%20such%20as%20hyphens%20and%20spaces%2C%20and%20a%20new%20script-only%20mode.%20Run%20it%20as-is%20and%20it%20will%20execute%20the%20revoke%20commands%2C%20but%20you%20can%20change%20the%20%40modeScriptOnly%26nbsp%3Bvariable%20to%20%221%22%20at%20the%20beginning%20of%20the%20script%20and%20it%20will%20only%20print%20the%20commands.%20Great%20job%2C%20Rick!%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fmsdnshared.blob.core.windows.net%2Fmedia%2FTNBlogsFS%2Fprod.evol.blogs.technet.com%2Ftelligent.evolution.components.attachments%2F01%2F7622%2F00%2F00%2F03%2F31%2F07%2F28%2FRemove%2520Public%2520and%2520Guest%2520Permissions%2C%2520v3.sql%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20Remove%20Public%20and%20Guest%20Permissions%2C%20v3.sql%20%3C%2FA%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383594%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20TECHNET%20on%20Feb%2004%2C%202010%20You%20can't%20get%20rid%20of%20the%20%22public%22%20role%20and%20by%20default%20in%20SQL%20Server%202005%20and%202008%20many%20objects%20have%20permissions%20granted%20to%20public.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383594%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerSecurity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 11:25 AM
Updated by: