SQL hardening

Home Page Forums SharePoint On-Premises 2013 SQL hardening

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Mike Varley Mike Varley 2 weeks, 6 days ago.

  • Author
    Posts
  • #653
    Profile photo of Mike Varley
    Mike Varley
    Participant

    Hi All

    We’ve been ‘presented’ with a hardened SQL 2014 box that is to form part of a new SP2013 farm. The create farm script fell at the first hurdle with an ‘EXECUTE denied on sp_addrole’ when trying to create the config DB. It transpires that the hardening includes revoking execute to stored procedures in MASTER for PUBLIC. Has anyone tried this before successfully? Will it break lots of other things too? The DBAs have granted dbcreator and secadmin to the setup account (they tell us!). The list of revokes is below.

    Thanks
    Mike

    PRINT ‘Revoking EXECUTE permission to public from dangerous procedures’

    USE master
    GO

    deny execute on sp_replwritetovarbin to public
    GO

    REVOKE EXECUTE ON sys.sp_add_agent_parameter TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_add_agent_profile TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_add_log_shipping_alert_job TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_add_log_shipping_primary_database TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_add_log_shipping_primary_secondary TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_add_log_shipping_secondary_database TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_add_log_shipping_secondary_primary TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addapprole TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addarticle TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_adddatatype TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_adddatatypemapping TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_adddistpublisher TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_adddistributiondb TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_adddistributor TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_adddynamicsnapshot_job TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addextendedproperty TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_AddFunctionalUnitToComponent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addlinkedserver TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addlinkedsrvlogin TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addlogin TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addlogreader_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addmergealternatepublisher TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addmergearticle TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addmergefilter TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addmergelogsettings TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addmergepartition TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addmergepublication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addmergepullsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addmergepullsubscription_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addmergepushsubscription_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addmergesubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addmessage TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addpublication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addpublication_snapshot TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addpullsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addpullsubscription_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addpushsubscription_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addqreader_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addqueued_artinfo TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addremotelogin TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addrole TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addrolemember TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addscriptexec TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addserver TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addsrvrolemember TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addsubscriber TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addsubscriber_schedule TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addsynctriggers TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addsynctriggerscore TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addtabletocontents TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addtype TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_addumpdevice TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_adduser TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_adjustpublisheridentityrange TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_altermessage TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_approlepassword TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_attach_db TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_attach_single_file_db TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_attachsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_change_agent_parameter TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_change_agent_profile TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_change_log_shipping_primary_database TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_change_log_shipping_secondary_database TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_change_log_shipping_secondary_primary TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_change_subscription_properties TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_change_users_login TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changearticle TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changearticlecolumndatatype TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changedbowner TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changedistpublisher TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changedistributiondb TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changedistributor_password TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changedistributor_property TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changedynamicsnapshot_job TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changelogreader_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changemergearticle TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changemergefilter TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changemergelogsettings TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changemergepublication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changemergepullsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changemergesubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changeobjectowner TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changepublication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changepublication_snapshot TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changeqreader_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changereplicationserverpasswords TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changesubscriber TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changesubscriber_schedule TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changesubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changesubscriptiondtsinfo TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_changesubstatus TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_cleanmergelogfiles TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_cleanup_log_shipping_history TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_configure TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_control_dbmasterkey_password TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_control_plan_guide TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_copymergesnapshot TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_copysnapshot TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_copysubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_create_plan_guide TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_createmergepalrole TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_createorphan TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_createstats TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_createtranpalrole TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dbfixedrolepermission TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_defaultdb TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_defaultlanguage TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_delete_http_namespace_reservation TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_delete_log_shipping_alert_job TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_delete_log_shipping_primary_database TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_delete_log_shipping_primary_secondary TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_delete_log_shipping_secondary_database TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_delete_log_shipping_secondary_primary TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_deletemergeconflictrow TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_deletepeerrequesthistory TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_deletetracertokenhistory TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_denylogin TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_drop_agent_parameter TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_drop_agent_profile TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropanonymousagent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropanonymoussubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropapprole TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_droparticle TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropdatatypemapping TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropdevice TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropdistpublisher TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropdistributiondb TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropdistributor TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropdynamicsnapshot_job TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropextendedproperty TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_droplinkedsrvlogin TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_droplogin TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropmergealternatepublisher TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropmergearticle TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropmergefilter TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropmergelogsettings TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropmergepartition TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropmergepublication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropmergepullsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropmergesubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropmessage TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_droporphans TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_droppublication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_droppublisher TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_droppullsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropremotelogin TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_droprole TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_droprolemember TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropserver TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropsrvrolemember TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropsubscriber TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_droptype TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_dropuser TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_enable_heterogeneous_subscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_enableagentoffload TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_enum_oledb_providers TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_enumcustomresolvers TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_enumdsn TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_enumeratependingschemachanges TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_enumerrorlogs TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_enumfullsubscribers TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_enumoledbdatasources TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_expired_subscription_cleanup TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_generatefilters TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_grant_publication_access TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_grantdbaccess TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_grantlogin TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_link_publication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_markpendingschemachange TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_marksubscriptionvalidation TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_mergearticlecolumn TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_mergecleanupmetadata TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_mergedummyupdate TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_mergemetadataretentioncleanup TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_mergesubscription_cleanup TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_mergesubscriptionsummary TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MS_replication_installed TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSacquireHeadofQueueLock TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSacquireserverresourcefordynamicsnapshot TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSacquireSlotLock TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSacquiresnapshotdeliverysessionlock TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSactivate_auto_sub TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSactivatelogbasedarticleobject TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSactivateprocedureexecutionarticleobject TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_anonymous_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_article TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_compensating_cmd TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_distribution_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_distribution_history TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_dynamic_snapshot_location TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_filteringcolumn TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_log_shipping_error_detail TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_log_shipping_history_detail TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_logreader_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_logreader_history TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_merge_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_merge_anonymous_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_merge_history TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_merge_history90 TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_merge_subscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_mergereplcommand TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_mergesubentry_indistdb TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_publication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_qreader_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_qreader_history TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_repl_alert TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_repl_command TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_repl_commands27hp TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_repl_error TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_replcmds_mcit TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_replmergealert TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_snapshot_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_snapshot_history TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_subscriber_info TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_subscriber_schedule TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_subscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_subscription_3rd TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_tracer_history TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadd_tracer_token TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddanonymousreplica TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadddynamicsnapshotjobatdistributor TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddguidcolumn TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddguidindex TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddinitialarticle TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddinitialpublication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddinitialschemaarticle TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddinitialsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddlightweightmergearticle TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddmergedynamicsnapshotjob TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddmergetriggers TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddmergetriggers_from_template TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddmergetriggers_internal TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddpeerlsn TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSaddsubscriptionarticles TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSadjust_pub_identity TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSallocate_new_identity_range TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSalreadyhavegeneration TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSarticlecleanup TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchange_article TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchange_distribution_agent_properties TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchange_logreader_agent_properties TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchange_merge_agent_properties TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchange_mergearticle TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchange_mergepublication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchange_priority TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchange_publication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchange_retention TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchange_retention_period_unit TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchange_snapshot_agent_properties TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchange_subscription_dts_info TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchangearticleresolver TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchangedynamicsnapshotjobatdistributor TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchangedynsnaplocationatdistributor TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSchangeobjectowner TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScleanup_agent_entry TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScleanup_conflict TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScleanup_publication_ADinfo TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScleanup_subscription_distside_entry TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScleanupdynamicsnapshotfolder TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScleanupdynsnapshotvws TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSCleanupForPullReinit TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScleanupmergepublisher_internal TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSclear_dynamic_snapshot_location TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSclearresetpartialsnapshotprogressbit TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreate_all_article_repl_views TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreate_article_repl_views TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreate_dist_tables TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreate_logical_record_views TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreate_sub_tables TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreatedisabledmltrigger TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreatedummygeneration TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreateglobalreplica TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreatelightweightinsertproc TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreatelightweightmultipurposeproc TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreatelightweightprocstriggersconstraints TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreatelightweightupdateproc TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreatemergedynamicsnapshot TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MScreateretry TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdelete_tracer_history TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdeletefoldercontents TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdeletemetadataactionrequest TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdeleteretry TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdeletetranconflictrow TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdelgenzero TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdelrow TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdelrowsbatch TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdelrowsbatch_downloadonly TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdelsubrows TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdelsubrowsbatch TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_6x_publication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_6x_replication_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_anonymous_entry TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_article TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_distribution_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_distribution_agentid_dbowner_proxy TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_dynamic_snapshot_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_logreader_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_merge_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_merge_subscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_publication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_qreader_history TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_snapshot_agent TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_snapshot_dirs TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_subscriber_info TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_subscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdrop_subscription_3rd TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdroparticleconstraints TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdroparticletombstones TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdropconstraints TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdropdynsnapshotvws TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdropfkreferencingarticle TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdropmergearticle TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdropmergedynamicsnapshotjob TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdropretry TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSdroptemptable TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSinsert_identity TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSinsertdeleteconflict TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSinserterrorlineage TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSinsertgenerationschemachanges TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSinsertgenhistory TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSinsertlightweightschemachange TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSinsertschemachange TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSkilldb TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdate_agenttype_default TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdate_singlelogicalrecordmetadata TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdate_subscriber_info TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdate_subscriber_schedule TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdate_subscriber_tracer_history TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdate_subscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdate_tracer_history TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdatecachedpeerlsn TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdategenhistory TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdateinitiallightweightsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdatelastsyncinfo TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdatepeerlsn TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdaterecgen TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdatereplicastate TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSupdatesysmergearticles TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_MSwritemergeperfcounter TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_posttracertoken TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_procoption TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_register_custom_scripting TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_registercustomresolver TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_reinitmergepullsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_reinitmergesubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_reinitpullsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_reinitsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_remoteoption TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_removedbreplication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_removedistpublisherdbreplication TO PUBLIC
    GO

    –REVOKE EXECUTE ON sys.sp_rename TO PUBLIC
    –GO

    REVOKE EXECUTE ON sys.sp_renamedb TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_revoke_publication_access TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_revokedbaccess TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_revokelogin TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_serveroption TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_subscription_cleanup TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_update_agent_profile TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_update_user_instance TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_updateextendedproperty TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_updatestats TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_upgrade_log_shipping TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_validatelogins TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_validatemergepublication TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_validatemergepullsubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.sp_validatemergesubscription TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.xp_dirtree TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.xp_fileexist TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.xp_fixeddrives TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.xp_getnetname TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.xp_grantlogin TO PUBLIC
    GO

    –REVOKE EXECUTE ON sys.xp_instance_regread TO PUBLIC
    –GO

    REVOKE EXECUTE ON sys.xp_msver TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.xp_regread TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.xp_revokelogin TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.xp_sprintf TO PUBLIC
    GO

    REVOKE EXECUTE ON sys.xp_sscanf TO PUBLIC
    GO

    • This topic was modified 1 month, 3 weeks ago by Profile photo of Mike Varley Mike Varley.
  • #670
    Profile photo of Mark Stokes
    Mark Stokes
    Keymaster

    Hi Mike,

    I have just seen this post. I will get my resident SQL expert to take a look for you.

    – Mark

  • #675
    Profile photo of Steve Dixon
    Steve Dixon
    Participant

    Hi Mike,

    There doesn’t appear to be any Official Documentation on what permissions you require on a SQL Instance to install and run SharePoint that I can find.

    Normally you would have 2 accounts for SharePoint, 1 to perform the install and 1 for running the service.

    As with Windows User Access Control (UAC), during the install or upgrade of SharePoint, you would temporarily grant the SharePoint Installer Account administrator rights to the SQL Instance (SysAdmin Role).
    This will allow it to perform the system actions it requires to create, update and secure the SharePoint databases.
    Once complete, the SharePoint Installer Account permissions can be revoked and the account disabled.

    You may also need to perform similar account elevation to the SharePoint Service Account when you want to create new SharePoint Content Databases.

    You will need to test that under this hardened SQL Instance, SharePoint Service Account has enough permissions to perform its tasks as it could be trying to access some system (master) Stored Procedures to complete its tasks.

    If you really want to harden the SQL Instance, then you should also look at running the SQL Services under Group Managed Service Accounts. That way, no one will ever know the service account passwords, even during install, and they are automatically scrambled every 30 days.
    See here :- https://blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/

    I hope that this helps.

    Thanks

    Steve Dixon
    BSc(Hons) IT & Comp, DipComp, DipIT, MBCS, MMSA

  • #676
    Profile photo of Mike Varley
    Mike Varley
    Participant

    Thanks Steve, Mark

    As it happens, in this instance we have managed to get a change approved to remove the hardening on SQL for the time being at least (just for SharePoint not across the board). Whether they will come back to us later in an attempt re-apply it remains to be seen.

    For install and config we have never had to resort to sysadmin on SQL (just DB creator and secadmin for the setup account) although we have requested it for testing suspected permissions issues.

    Thanks for your help.
    Cheers
    Mike

You must be logged in to reply to this topic.