Public Not Granted Server Permissions

The pre-defined policies that come with SQL Server 2008 include one called "Public Not Granted Server Permissions". Running this policy in evaluation mode on a default installation will show a non-compliance failure due to 5 server-level permissions that are assigned to the public role during installation.

Removal of these 5 permissions will make your server pass the "Public Not Granted Server Permissions" and is also required to comply with the Department of Defense's Database STIG (Security Technical Implementation Guide), requirements DG0080 and DG0119:

(DG0080: CAT II) The DBA will ensure privileges granted to application user database accounts are restricted to those required to perform the specific application functions.

(DG0119: CAT II) The DBA will ensure database application user roles are restricted to select, insert, update, delete, and execute privileges.

If you just want to quickly get rid of the offending permissions, here's the code:

REVOKE VIEW ANY DATABASE FROM public;
REVOKE CONNECT ON ENDPOINT::[TSQL Local Machine] FROM public;
REVOKE CONNECT ON ENDPOINT::[TSQL Named Pipes] FROM public;
REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] FROM public;
REVOKE CONNECT ON ENDPOINT::[TSQL Default VIA] FROM public;

If you don't have the Micosoft policies installed in SQL Server Management Studio (SSMS), my last post explains how to install them and start to examine them.

I've made several attempts to add this info to the relevant MSDN article, but so far I keep running into a page-load error.

Oh, you can also remove these permissions by pointing and clicking in SSMS, but it's more trouble than its worth. You get rid of VIEW ANY DATABASE by opening the Object Explorer, right-click on the Server node and choosing Properties, then click on the Permissions page, click the Search... button, enter "public" and Okay it, select the "public" line now in the "Logins and roles" section, then scroll down in the "Permissions for public" section and find "View any database" and uncheck the box under "Grant", and Okay it. Whew.

Next, expand the server node in Object Explorer, then expand the Security node, then the "Server Roles" node, then double-click on "public" (or right-click and choose Properties), then click on the Permissions page, and if the 4 Endpoint permissions sitll exist, they'll be listed in the "Securables" section. Select each securable one at a time, and in the "Permissions" section, remove the check box under "Grant" for the "Connect" permission and Okay it. You're done. Or if you just run the code above, you were done a long time ago...

If you want to check server level and endpoint permissions in SQL Server 2008, here's a query I use a lot:

SELECT perm.class_desc, perm.permission_name, endpoint_name = e.name, perm.state_desc,
grantor = prin1.name, grantee = prin2.name
FROM master.sys.server_permissions perm
INNER JOIN master.sys.server_principals prin1 ON perm.grantor_principal_id = prin1.principal_id
INNER JOIN master.sys.server_principals prin2 ON perm.grantee_principal_id = prin2.principal_id
LEFT JOIN master.sys.endpoints e ON perm.major_id = e.endpoint_id

 

 

Identify and remove the default server permissions from the public role.sql

Comments

• Anonymous
  April 29, 2014
  Hello Quantum, this is a very simple, straight to the point and powerful post you wrote. I randomly came across it and looking forward to implementing it in our production environment. Thanks.
• Anonymous
  November 04, 2014
  Do not implement this in production environment because no login/user can connect to SQL server anymore, except sysadmin.
• Anonymous
  April 06, 2016
  I don't see that the user can connect if you remove connect from the public role.