public server role

In exploring permissions that users have, I find that they all have VIEW ANY DATABASE permission which they inherit from the public server role. You can see this by selecting the Permissions page on the Server Properties dialog and highlighting "public". The permission shows as having been granted by sa. This is listed as a server role. However, it does not show in the list of server roles and I can't find any documentation for it (RTM BOL). Interestingly, if I revoke this permission (which is the only permission this role has), the public server role disappears from view. But I can subsequently regrant the permission with Transact-SQL in master and the role comes back.

I would like more information about this role. It seems to be sort of "secret".

When I revoke the permission, users can't see any databases except master and tempdb (both of which have active guest users) even though they have been granted access to other databases.

What I was trying to accomplish by changing this permission was to allow a user to see only those databases which they are allowed to use. But that does not seem to be possible.

Thanks for the help.

Sharon

All logins belong to the public server role. It's like the "Everyone" group in Windows. There is a bug filed against Management Studio for not displaying this role in the list of fixed server roles.

The VIEW ANY DATABASE permission is assigned to public for backward compatibility with SQL Server 2000.

Here are a couple of BOL articles that mention this role:

http://msdn2.microsoft.com/en-us/library/ms175892(SQL.90).aspx

http://msdn2.microsoft.com/en-us/library/ms187096(SQL.90).aspx

Thanks
Laurentiu

|||

The role does not show in sp_helpsrvrole, either. I didn't have time to look for an appropriate catalog view, so there may be one that shows it.

Sharon

|||

Catalogs show it: sys.server_principals.

You can see the permissions granted to it in sys.server_permissions.

Thanks
Laurentiu