I have been setting up policies on my 2008 servers. I thought a good place to start would be with the suggested policies Microsoft provides with their samples. You can download the sample databases from codeplex.
I can came across a sample policy named “Public Not Granted Server Permission”, now this sounds like something a person would want to implement. I imported the policy and ran it on my test system and discovered my system failed, but I was not really sure why. The policy explains that the server role public has been granted server level permissions. OK, but what does that mean? What server level permissions? This policy does not provide an option to force compliance, so the dba must correct the issue manually, but how and if you correct what permissions will be revoked?
I started by trying to investigate the @PublicServerRoleIsGrantedPermissions facet, but that provided no greater information. Then I found this great blog post by Randy Dyess who delves into the guts of the public role. He provides a script to identify the permissions that the public role has been granted. Using this script I determined that a fresh install of SQL Server by default grants the public role the following permissions:
| Name | Permission | Class | Securable | State |
| public | CONNECT | ENDPOINT | NAMED_PIPES | GRANT |
| public | CONNECT | ENDPOINT | SHARED_MEMORY | GRANT |
| public | CONNECT | ENDPOINT | TCP | GRANT |
| public | CONNECT | ENDPOINT | VIA | GRANT |
| public | VIEW ANY DATABASE | SERVER | Server | GRANT |
Of these permissions only VIEW ANY DATABASE is the only server lever permission. I am familiar with this permission. If you DENY VIEW ANY DATABASE to PUBLIC a user will only be able to see the master, msdb and databases in which they own. This would prevent a user from seeing the other databases on the server listed in management studio. The problem with this is that I don’t make any user dbo. If a developer wanted to connect via Studio Manager they would not see database which they have been granted permission within. They can still connect to the database, but can’t see it in Management Studio which kind of defeats the point.
For my testing I went ahead and issued the following:
DENY VIEW ANY DATABASE to PUBLIC.
I re-evaluated the policy and it still failed. So on the advice of Brian Kelley, I issued the denied all connect permissions to the Endpoints.
DENY CONNECT ON ENDPOINT::[TSQL Named Pipes] to public
DENY CONNECT ON ENDPOINT::[TSQL Local Machine] to public
DENY CONNECT ON ENDPOINT::[TSQL Default TCP] to public
DENY CONNECT ON ENDPOINT::[TSQL Default VIA] to public
This worked and the policy passed. However, this is not a policy that I will be implementing.
