Recommendations and Guidelines on Granting Server Permissions to Public role

Article ID: 2160698 - View products that this article applies to.
Expand all | Collapse all

Summary

Every SQL Server login belongs to the Public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. For this reason, care must be taken when granting permissions to Public server role especially when granting server-level permissions.

For Example, a newly created login which is not given any additional privileges, does not have permissions to execute the "Create Database" command to create a database. However, if CONTROL SERVER permission is granted to the Public server role explicitly, the newly created SQL Server login which is a member of Public server role can now create the database even when the login does not have explicit permissions to create the database.

More Information

  

Rule software

Rule title

Rule description

Product versions against which the rule is evaluated 

SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)

Server public permissions

The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect if additional server-level permissions have been granted to the Public server role explicitly. If you run the BPA tool and encounter a warning with the title of Engine - Server public permissions, then you have granted additional server-level permission to Public server role.

The public role is granted VIEW ANY DATABASE permission by default. For more information refer to the Books Online topic VIEW ANY DATABASE Permission

You can also run the following query to determine if additional server-level permissions are granted to the Public server role.

SELECT * FROM master.sys.server_permissions Where grantee_principal_id = SUSER_SID(N'public')

For a list of Server-level permissions, refer to sys.server_permissions catalog view.

SQL Server 2008
SQL Server 2008 R2


SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)

Server public permissions

The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect if additional server-level permissions have been granted to the Public server role explicitly. If you run the BPA tool and encounter a warning with the title of Engine - Server public permissions, then you have granted additional server-level permission to Public server role.

The public role is granted VIEW ANY DATABASE permission by default. For more information refer to the Books Online topic VIEW ANY DATABASE Permission

You can also run the following query to determine if additional server-level permissions are granted to the Public server role.

SELECT * FROM master.sys.server_permissions Where grantee_principal_id = SUSER_SID(N'public')

For a list of Server-level permissions, refer to sys.server_permissions catalog view.

SQL Server 2012 

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2160698 - Last Review: April 4, 2012 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 Standard
Keywords: 
KB2160698

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com