locked
Only users with sysadmin server role can access databases after making some changes to the public role permissions RRS feed

  • Question

  • Hello,

    I made some minor changes (or at least what I thought were minor) to the Public Role permissions on the system tables.  Now only users that are assigned to the sysadmin role can connect to the databases.  Other users (even those that are assigned to the dbo role for the particular database they are attempting to access) are being refused with the following event viewer error Login failed for user 'XXXXXX\XXXXXXX'. [CLIENT: <named pipe>] event id: 18456

    I have tried to reverse what I did, but that does not seem to help.  Is there a way to restore default Public role permissions?  Or are there particular things that I should be looking at?  Any help would be appreciated.

    Thank you

    Monday, November 24, 2008 11:03 PM

All replies

  • Hi,

     

    Check if you accidently removed the permission to connect to endpoint:

     

    SELECT

               ep.protocol_desc

               , suser_name(p.grantee_principal_id) AS Principal

               , p.permission_name

               , p.state_desc

    FROM sys.server_permissions p, sys.endpoints ep

    WHERE p.class = 105

    AND p.major_id = ep.endpoint_id

     

    /Elisabeth

    Tuesday, November 25, 2008 8:13 AM
  • Yikes! You don't want to mess with default roles, but I suppose you know that now Wink

    Assuming that named pipes is the protocol you want clients to use (otherwise it's a network protocol config issue) and that you've only change the role permissions on a single user database and not a system db or system role AND you're using SQL 2005 you may be able to get things back in line by granting the select permission to the role on the following system views (sys schema):

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

    So for example you'd run:
    GRANT SELECT ON sys.
    xml_schema_wildcards TO [public];
    ...and so on for each view.

    Good luck!

    Monday, December 1, 2008 10:28 PM