August 16, 2010 at 8:45 am
Is there any script that can revoke select permission on databases/objects from public at a time.
Appreciate your help.
I have got some 1300+ low vulnerabilities in SQL 2005 production cluster server. I am posting some of them below.
I am looking for a one single script , which can revoke permission granted to public. Thanks in advance.
Vulnerability Detail: (Granted To=public) (Granted By=dbo) (Privilege=SELECT) (Database=master) (Table Name=TABLE_PRIVILEGES) (Schema Name=INFORMATION_SCHEMA)
Vulnerability Detail: (Granted To=public) (Granted By=dbo) (Privilege=SELECT) (Database=master) (Table Name=dm_os_hosts) (Schema Name=sys)
Vulnerability Detail: (Granted To=public) (Granted By=dbo) (Privilege=SELECT) (Database=master) (Table Name=openkeys) (Schema Name=sys)
Vulnerability Detail: (Granted To=public) (Granted By=dbo) (Privilege=SELECT) (Database=master) (Table Name=dm_os_memory_allocations) (Schema Name=sys)
Vulnerability Detail: (Granted To=public) (Granted By=dbo) (Privilege=SELECT) (Database=master) (Table Name=dm_os_loaded_modules) (Schema Name=sys)
Vulnerability Detail: (Granted To=public) (Granted By=dbo) (Privilege=SELECT) (Database=master) (Table Name=dm_db_task_space_usage) (Schema Name=sys)
Vulnerability Detail: (Granted To=public) (Granted By=dbo) (Privilege=SELECT) (Database=master) (Table Name=dm_os_memory_objects) (Schema Name=sys)
Vulnerability Detail: (Granted To=public) (Granted By=dbo) (Privilege=SELECT) (Database=master) (Table Name=DOMAINS) (Schema Name=INFORMATION_SCHEMA)
September 5, 2010 at 11:25 am
Hi,
Be careful with revoking permissions from public role. It captures all default permissions for users in a database and you may have issues if you remove all permissions. Permissions should not be granted to the public role, but if you want to remove any default permisions make sure you test it first.
September 5, 2010 at 12:41 pm
Public doesn't have rights to select from tables unless you granted them. And as noted above, you should not grant rights to public.
Before you remove them, however, you might want to be sure that you create a role and transfer permissions to this role that are on public. This will help you keep breakage to a minimum.
In terms of revoking permissions, why not script permissions, then change the GRANT to a REVOKE in that script.
September 6, 2010 at 8:16 am
Hello,
This might be useful http://duartes.org/gustavo/blog/post/lock-down-sql-server-2005
HTH,
Cheers !
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply