How To Remove Public and Guest Permissions in SQL Server 2005 and 2008?
In SQL Server 2005 and 2008 versions, most objects have public permissions granted by default and you can't get rid of the "public" role easily. And, it is quite possible that you may think these permissions are required for the SQL Server to work properly. But that is not the case, some of these permissions could cause security issues and can be removed.
But SQL Server Config does not allow you to delete the public or guest permissions so that you do not accidentally delete some critical objects or change a permission. Now if you try to remove the permissions one-by-one, you will end up in a mess as there are many permissions to be deleted and you will not be sure which one to delete.
Here is a cool script that helps you remove the permissions that can safely disappear. The script does the following:
- Revokes the VIEW ANY DATABASE permission from the master database.
- Revokes all object privileges assigned to public or guest for every database.
- Revokes the connect permission from guest on all databases except master and tempdb.
Download The Script: Remove public and guest permissions.sql
Thanks JD Lambert