Public Role In sql 2005

Question

Can anybody let me know that what access does public role have in SQL 2005.

Thanks

Answer 1

http://www.google.com/search?hl=en&num=100&q=public+role+sql+server+2005&aq=f&oq=&aqi=g-c1

edit

but anyway here we go The public role is granted VIEW ANY DATABASE permission.

Answer 2

All logins belong to the Public role by default. Unless you change it, the public role has no permissions granted to it during installation / configuration of the server.

Answer 3

Just saying "The public role is granted VIEW ANY DATABASE permission" is not the full picture.

In SQL Server 2005, I created a fresh database and then ran sp_helprotect. Here are the results (filtered for "public"):

SQL Server 2005

Select on sys.all_columns
Select on sys.all_objects
Select on sys.all_parameters
Select on sys.all_sql_modules
Select on sys.all_views
Select on sys.allocation_units
Select on sys.assemblies
Select on sys.assembly_files
Select on sys.assembly_modules
Select on sys.assembly_references
Select on sys.assembly_types
Select on sys.asymmetric_keys
Select on sys.certificates
Select on sys.check_constraints
Select on sys.column_type_usages
Select on sys.column_xml_schema_collection_usages
Select on sys.columns
Select on sys.computed_columns
Select on sys.conversation_endpoints
Select on sys.conversation_groups
Select on sys.crypt_properties
Select on sys.data_spaces
Select on sys.database_files
Select on sys.database_permissions
Select on sys.database_principal_aliases
Select on sys.database_principals
Select on sys.database_role_members
Select on sys.default_constraints
Select on sys.destination_data_spaces
Select on sys.event_notifications
Select on sys.events
Select on sys.extended_procedures
Select on sys.extended_properties
Select on sys.filegroups
Select on sys.foreign_key_columns
Select on sys.foreign_keys
Select on sys.fulltext_catalogs
Select on sys.fulltext_index_catalog_usages
Select on sys.fulltext_index_columns
Select on sys.fulltext_indexes
Select on sys.identity_columns
Select on sys.index_columns
Select on sys.indexes
Select on sys.internal_tables
Select on sys.key_constraints
Select on sys.key_encryptions
Select on sys.message_type_xml_schema_collection_usages
Select on sys.module_assembly_usages
Select on sys.numbered_procedure_parameters
Select on sys.numbered_procedures
Select on sys.objects
Select on sys.parameter_type_usages
Select on sys.parameter_xml_schema_collection_usages
Select on sys.parameters
Select on sys.partition_functions
Select on sys.partition_parameters
Select on sys.partition_range_values
Select on sys.partition_schemes
Select on sys.partitions
Select on sys.plan_guides
Select on sys.procedures
Select on sys.remote_service_bindings
Select on sys.routes
Select on sys.schemas
Select on sys.service_contract_message_usages
Select on sys.service_contract_usages
Select on sys.service_contracts
Select on sys.service_message_types
Select on sys.service_queue_usages
Select on sys.service_queues
Select on sys.services
Select on sys.sql_dependencies
Select on sys.sql_modules
Select on sys.stats
Select on sys.stats_columns
Select on sys.symmetric_keys
Select on sys.synonyms
Select on sys.syscolumns
Select on sys.syscomments
Select on sys.sysconstraints
Select on sys.sysdepends
Select on sys.sysfilegroups
Select on sys.sysfiles
Select on sys.sysforeignkeys
Select on sys.sysfulltextcatalogs
Select on sys.sysindexes
Select on sys.sysindexkeys
Select on sys.sysmembers
Select on sys.sysobjects
Select on sys.syspermissions
Select on sys.sysprotects
Select on sys.sysreferences
Select on sys.syssegments
Select on sys.system_columns
Select on sys.system_objects
Select on sys.system_parameters
Select on sys.system_sql_modules
Select on sys.system_views
Select on sys.systypes
Select on sys.sysusers
Select on sys.tables
Select on sys.transmission_queue
Select on sys.trigger_events
Select on sys.triggers
Select on sys.type_assembly_usages
Select on sys.types
Select on sys.views
Select on sys.xml_indexes
Select on sys.xml_schema_attributes
Select on sys.xml_schema_collections
Select on sys.xml_schema_component_placements
Select on sys.xml_schema_components
Select on sys.xml_schema_elements
Select on sys.xml_schema_facets
Select on sys.xml_schema_model_groups
Select on sys.xml_schema_namespaces
Select on sys.xml_schema_types
Select on sys.xml_schema_wildcard_namespaces
Select on sys.xml_schema_wildcards

---

Here are the same results for 2008:

SQL Server 2008

Select on sys.all_columns
Select on sys.all_objects
Select on sys.all_parameters
Select on sys.all_sql_modules
Select on sys.all_views
Select on sys.allocation_units
Select on sys.assemblies
Select on sys.assembly_files
Select on sys.assembly_modules
Select on sys.assembly_references
Select on sys.assembly_types
Select on sys.asymmetric_keys
Select on sys.certificates
Select on sys.change_tracking_tables
Select on sys.check_constraints
Select on sys.column_type_usages
Select on sys.column_xml_schema_collection_usages
Select on sys.columns
Select on sys.computed_columns
Select on sys.conversation_endpoints
Select on sys.conversation_groups
Select on sys.conversation_priorities
Select on sys.crypt_properties
Select on sys.data_spaces
Select on sys.database_audit_specification_details
Select on sys.database_audit_specifications
Select on sys.database_files
Select on sys.database_permissions
Select on sys.database_principal_aliases
Select on sys.database_principals
Select on sys.database_role_members
Select on sys.default_constraints
Select on sys.destination_data_spaces
Select on sys.event_notifications
Select on sys.events
Select on sys.extended_procedures
Select on sys.extended_properties
Select on sys.filegroups
Select on sys.foreign_key_columns
Select on sys.foreign_keys
Select on sys.fulltext_catalogs
Select on sys.fulltext_index_catalog_usages
Select on sys.fulltext_index_columns
Select on sys.fulltext_index_fragments
Select on sys.fulltext_indexes
Select on sys.fulltext_stoplists
Select on sys.fulltext_stopwords
Select on sys.function_order_columns
Select on sys.identity_columns
Select on sys.index_columns
Select on sys.indexes
Select on sys.internal_tables
Select on sys.key_constraints
Select on sys.key_encryptions
Select on sys.message_type_xml_schema_collection_usages
Select on sys.module_assembly_usages
Select on sys.numbered_procedure_parameters
Select on sys.numbered_procedures
Select on sys.objects
Select on sys.parameter_type_usages
Select on sys.parameter_xml_schema_collection_usages
Select on sys.parameters
Select on sys.partition_functions
Select on sys.partition_parameters
Select on sys.partition_range_values
Select on sys.partition_schemes
Select on sys.partitions
Select on sys.plan_guides
Select on sys.procedures
Select on sys.remote_service_bindings
Select on sys.routes
Select on sys.schemas
Select on sys.service_contract_message_usages
Select on sys.service_contract_usages
Select on sys.service_contracts
Select on sys.service_message_types
Select on sys.service_queue_usages
Select on sys.service_queues
Select on sys.services
Select on sys.spatial_index_tessellations
Select on sys.spatial_indexes
Select on sys.sql_dependencies
Select on sys.sql_modules
Select on sys.stats
Select on sys.stats_columns
Select on sys.symmetric_keys
Select on sys.synonyms
Select on sys.syscolumns
Select on sys.syscomments
Select on sys.sysconstraints
Select on sys.sysdepends
Select on sys.sysfilegroups
Select on sys.sysfiles
Select on sys.sysforeignkeys
Select on sys.sysfulltextcatalogs
Select on sys.sysindexes
Select on sys.sysindexkeys
Select on sys.sysmembers
Select on sys.sysobjects
Select on sys.syspermissions
Select on sys.sysprotects
Select on sys.sysreferences
Select on sys.system_columns
Select on sys.system_objects
Select on sys.system_parameters
Select on sys.system_sql_modules
Select on sys.system_views
Select on sys.systypes
Select on sys.sysusers
Select on sys.table_types
Select on sys.tables
Select on sys.transmission_queue
Select on sys.trigger_events
Select on sys.triggers
Select on sys.type_assembly_usages
Select on sys.types
Select on sys.views
Select on sys.xml_indexes
Select on sys.xml_schema_attributes
Select on sys.xml_schema_collections
Select on sys.xml_schema_component_placements
Select on sys.xml_schema_components
Select on sys.xml_schema_elements
Select on sys.xml_schema_facets
Select on sys.xml_schema_model_groups
Select on sys.xml_schema_namespaces
Select on sys.xml_schema_types
Select on sys.xml_schema_wildcard_namespaces
Select on sys.xml_schema_wildcards

Answer 4

Thank you very much for all your answers. I really appreciate your help.