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,

Rogers,

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

RLF
"Rogers" <naissani (AT) hotmail (DOT) com> wrote

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 (AT) nomail (DOT) com> wrote

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/downlo...pt#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 (AT) hotmail (DOT) com> wrote

Thanks alot, I am using SQL Server 2000.

Thanks

"Russell Fields" <russellfields (AT) nomail (DOT) com> wrote

Please send me the list If you find any like which system objects in Master
and MSDB can be revoked from Public Role, and no impact on sql server.

Thanks alot for your help.

"Russell Fields" <russellfields (AT) nomail (DOT) com> wrote