Re: revoking all the privileges from public

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Rogers,

You don't say which version of SQL Server you are running, which may make
some difference.

First of all, you can revoke some rights in master and msdb and still have
the necessary things work, but revoking everything would be a disaster.
Basic system stored procedures that everyone needs would also be revoked.

You need to be more measured in your revoking of rights, which means you
need to understand what is still needed and what can be safely revoke. (If
you are using SQL Server 2005, note that some rights that were exposed in
SQL Server 2000 are now more tightly guarded.)

Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago, which
does suggest some areas for rights to be removed:
http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
Tools such as AuditPro also make suggestions.

So, I do not have a specific list for you, review the things in the BOL (or
do "exec sp_helprotect @username='public'" in both master and msdb) to see
what is granted, then think about whether you want that or not. (It is
pointless to manipulate rights in tempdb, since it is rebuilt everytime the
SQL Server restarts and it has its own mechanism for privacy.)

I tried finding a real solid article on this, but (alas) could not.

RLF

Note: The following article offers no guidance on what to revoke and what
not to revoke, it merely tells you what you can do, not what it is wise to
do: http://support.microsoft.com/kb/75291


"Rogers" <naissani@xxxxxxxxxxx> wrote in message
news:Oqsq$FD4HHA.3900@xxxxxxxxxxxxxxxxxxxxxxx
Thanks alot for your reply,

Is there any Microsoft article on that so I can present this to our
Manager and also refer that Microsoft doesn't recommend that.

Thanks

"Russell Fields" <russellfields@xxxxxxxxxx> wrote in message
news:eMITWCD4HHA.5776@xxxxxxxxxxxxxxxxxxxxxxx
Rogers,

Do not revoke all rights from public for these databases. That will
break many things.

RLF
"Rogers" <naissani@xxxxxxxxxxx> wrote in message
news:eLXU7qB4HHA.5804@xxxxxxxxxxxxxxxxxxxxxxx
I am revoking all the privileges from public role from master and tempdb
database, will that be any thing wrong after that? just want expert idea
because I ran one tool and it gave me suggession to remove public rights
from system objects on master database.

LIke following are some of the commands

USE MASTER
REVOKE EXEC ON dt_droppropertiesbyid FROM PUBLIC
GO

REVOKE EXEC ON dt_checkinobject FROM PUBLIC
GO

REVOKE SELECT ON dtproperties FROM PUBLIC
GO

REVOKE EXEC ON dt_checkoutobject FROM PUBLIC
GO

REVOKE EXEC ON dt_checkoutobject_u FROM PUBLIC
GO


Please give any suggession,







.

Relevant Pages

  • Re: revoking all the privileges from public
    ... Please send me the list If you find any like which system objects in Master ... You don't say which version of SQL Server you are running, ... you can revoke some rights in master and msdb and still have ...
    (microsoft.public.sqlserver.clients)
  • RE: [Maybe spam] Re: Relation of OS user to Informix database
    ... user who has those rights because they're a member of public? ... then you can't revoke their rights without revoking public's ... > RESOURCE permissions from the informix DBA user. ... When you created the database, was a MODE ANSI database, or a logged or ...
    (comp.databases.informix)
  • RE: [Maybe spam] Re: Relation of OS user to Informix database
    ... You can only revoke a privilege that exists explicitly and you are the ... if a user's rights are part of "public" then you cannot prevent the ... >> RESOURCE permissions from the informix DBA user. ... >When you created the database, was a MODE ANSI database, or a logged or ...
    (comp.databases.informix)
  • Re: revoking all the privileges from public
    ... You don't say which version of SQL Server you are running, ... you can revoke some rights in master and msdb and still have ...
    (microsoft.public.sqlserver.clients)
  • Re: What Is God?
    ... Societies that practised human sacrifice were quite willing to revoke ... >Governments exist to insure rights, ... No God... ...
    (comp.os.linux.networking)