revoke select permission on databases from public

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

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

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

  • 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