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. Only assign public permissions on any object when you want the object to be available to all users. You cannot change membership in public.
http://technet.microsoft.com/en-us/library/ms188659.aspx

If you do any of the following you will either create or add permissions to the Public Role

Database Tuning Advisor

Gives Public access to section of sp_DTA stored procedures in MSDB

SQL Server 2012 SSIS Catalog

Creating the SSISDB, Public has access to numerous sql stored procedures, tables, views, clr stored procedures in both the Internal nad catalog schema for SSISDB

Creating a Database Diagram

Creates a collection of SQL stored procedures and scalar functions in the database that the diagram was created for.

SharePoint 2007 and 2010

Allows Public Access to the Config database user tables (services, objects) .
Using Visual Studio Server Explorer
found this on an older server and found references to the stored procedures on the web they are all start dt_

Performance Dashboards

The 2008 and 2012 Performance dashboards create some stored procedures and functions in MSDB.

There are going to obviously be a lot more 3rd party apps that give access out, but these are the few that I have found along the way.

The nicest query I have found (certainly made me stop trying to write one!) is by Basit A. Farooq and can be found: http://www.sswug.org/articles/memberarticle.aspx?id=68403

Running it against a instance that had clearly had DTA and the Performance Dashboards run on it resulted in:

publicRole1

Having both the GRANT and REVOKE scripted out with it is pretty handy, I just copied it into Excel so you can then sort it via Object Type (none of that, cant Drop X its used by Y sillyness!) and your good to go.. (and bring it all back if you did need it!)

Advertisements