SQL Server 2000: Permissions on System Tables Granted to Logins Due to the Public Role

Microsoft SQL Server 7.0 and 2000 make use of the concept of roles at the server level and within each database. The public role is one of the built-in roles in each database and this corresponds to everyone who has access to that database. While the use of the public role is frowned upon for user-created objects, the role itself has permission to system tables and views, especially within the master and msdb databases. In this paper I will cover the access rights to system tables the public role has in these two system databases as well as in a typical user database. I'll also cover how the guest user adds to the conundrum, especially with respect to the system databases and cross-database ownership chaining. Finally, I'll look at what permissions can be revoked from the public role in each database and what the consequences are, both from a practical perspective (typical applications) to an extreme example (Microsoft's OpenHack 4 configuration).