Public role and guest security concern in SQL 2000 SP4

Hi all,

I have setup a new SQL 2000 SP4 and internal auditor query about revoke permission from Public role and remove guest from all databases.

1. Can I revoke all default permissions (select on system tables in all DBs) from "Public" role? I am concern any error after such action.

2. I found that guest account in DB -- master, tempdb and msdb. According to Microsoft documents. The account should not remove and can't from master and tempdb. How about msdb?

Thanks,

Regards,

Edwin

1. You may of course get errors from users trying to access system tables without being specifically granted access. You can resolve these issues by granting access to those users.

2. guest cannot be actually dropped - it can only be denied access to the database (hasdbaccess will show as 0). The msdb database is used by replication and SQL agent, among other components. You should check on the respective forums to see the impact of disabling guest access to the msdb database: SQL Server Replication and SQL Server Tools General.

Thanks
Laurentiu

|||

Thanks Laurentiu.

1. I have a search on web, some people mentioned that if revoke the default privilege from "Public" role on DBs (inclu. select system table, execute stored proc.). Microsoft wouldn't support my issues on this SQL in future. Is it true?

Thanks!

Edwin

|||

Microsoft may not be able to provide support if the user directly modifies system tables. Since all you are doing is changing permissions, your product will still be supported. Keep in mind that some features may require "public" to function properly. I can't think of any off the top of my head so once you come up with this locked down configuration you will have to do some verification testing to ensure that the functionality that you require works.

|||

As long as the changes that you make are made using documented features (revoking a permission using REVOKE statement is a documented feature), then you don't have to worry about invalidating your support options.

You should avoid making changes using undocumented techniques, such as, for example, directly updating system tables.

Thanks
Laurentiu

Public and Guest

I've got a 2000 server that I want to make sure is within best practices for
Sql Server 2000. I know that "permissions granted to the public role are
applied to
all users in the database and permissions granted to the guest user
are used by all users who do not have a user account in the database."
The way that my 2000 servers appear to be set up, presumably by default and
no intervention on my own, are with the guest account assigned to the public
role. As far as I can tell the public account just has standard select
access to the system tables and nothing else.
Do I have these accounts, public and guest, set up correctly? Is there
something I should do or check? Also, is there anything I should watch out
for?
Thx...Public is what it sounds like - everybody. All users are part of the public
group so any privileges you grant to public are granted to everybody who
hasn't been deny'ed the privilege. Guest, as you said, is the user that
anyone who doesn't have a user in the database is mapped to. You can assign
any privileges you feel are appropriate for a random anonymous user to have.
I recommend explicitly Denying privileges to the guest user so they don't
accidentally get permissions that someone grants to public.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F3C64B72-F96C-4052-AEA9-BEB4BFBAA323@.microsoft.com...
> I've got a 2000 server that I want to make sure is within best practices
> for
> Sql Server 2000. I know that "permissions granted to the public role are
> applied to
> all users in the database and permissions granted to the guest user
> are used by all users who do not have a user account in the database."
> The way that my 2000 servers appear to be set up, presumably by default
> and
> no intervention on my own, are with the guest account assigned to the
> public
> role. As far as I can tell the public account just has standard select
> access to the system tables and nothing else.
> Do I have these accounts, public and guest, set up correctly? Is there
> something I should do or check? Also, is there anything I should watch
> out
> for?
> Thx...|||To add to Roger's response, the guest account exists in all databases but is
enabled by default only in system databases. Unless you explicitly enable
the guest user (sp_adduser 'guest'), no database access will be not be
allowed until you explicitly added the user to the database. Personally, I
only enable the guest user in SQL 2000 in the special case where
cross-database chaining is used.
Hope this helps.
Dan Guzman
SQL Server MVP
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F3C64B72-F96C-4052-AEA9-BEB4BFBAA323@.microsoft.com...
> I've got a 2000 server that I want to make sure is within best practices
> for
> Sql Server 2000. I know that "permissions granted to the public role are
> applied to
> all users in the database and permissions granted to the guest user
> are used by all users who do not have a user account in the database."
> The way that my 2000 servers appear to be set up, presumably by default
> and
> no intervention on my own, are with the guest account assigned to the
> public
> role. As far as I can tell the public account just has standard select
> access to the system tables and nothing else.
> Do I have these accounts, public and guest, set up correctly? Is there
> something I should do or check? Also, is there anything I should watch
> out
> for?
> Thx...