其人氣雄貌偉，精通兵法，尤善騎射，有百步穿楊之能。劉昌祚的父親劉賀，在定川之戰中犧牲，劉昌祚得以父勛被朝廷錄為右班殿直主，後遷為西路都巡檢。出使遼國回朝後，宋神宗親自考試其馳射，百發百中。宋神宗當即授其通事舍人之職。夏兵入侵劉溝堡，劉昌祚率騎兩千出援，敵人在黑山設下萬騎埋伏，先以小隊詐敗，誘劉昌祚深入，結果劉昌祚他們被重軍圍困，兩軍廝殺，難分勝負。黃昏時分，夏兵主帥騎馬前沖，企圖活捉劉昌祚，劉昌祚一箭射去，正中敵帥咽喉，跌馬而亡。夏兵見主帥已死，紛紛潰逃。戰後，宋軍主帥李師中向朝廷報劉昌祚為頭功，被任為階州知州。後入蜀破羌有功，加皇城使、榮州刺史、秦鳳路鈐轄，又加西上闔門使、果州團練使、河州知州。
元豐四年（1081年），劉昌祚任涇原副都總管。宋軍西征，詔命劉昌祚與總管姚麟率兵五萬，在環慶都總管高遵裕的統一指揮下，涇原、環慶兩部合軍西進。劉昌祚部已入夏境，而高遵裕部卻遲遲不到。劉昌祚只好孤軍深入，到達磨臍隘後夏兵十萬據險抵抗，宋軍不能前進。劉昌祚手執兩盾，奮勇向前，率先登關，夏兵懼之而稍退，劉昌祚揮師而上，乘勝掩殺，斬敵一千七百餘人。又進鳴少川，取其地窖存糧。兵至靈州城，夏人城門尚未來得及關上，宋軍先鋒奪門，幾乎就要攻入城內，這時高遵裕快馬遣使，阻其攻城。劉昌祚只好下令暫不攻城。這天晚上，高遵裕在距靈州城三十里的地方遭遇夏兵，劉昌祚得信後派兵數千前去救援。夏兵撤走後，高遵裕嫉妒劉昌祚，想以應援遲緩之名殺掉劉昌祚，被部下勸止後，就解除劉的兵權。高遵裕圍城十八日而不能下，反被夏人決開七級渠灌淹，宋軍潰敗。高遵裕命劉昌祚斷後，把失敗罪責強加於劉昌祚。元豐五年（1082年），又移官涇原，加龍神衛四廂都指揮使，知延州。劉昌祚大修馬政，建復堡砦，很快振興當地軍事，屢破入侵夏兵，殺其統軍二人。又在隴山募軍屯田，加強了軍力。再遷殿前副指揮使、冀州觀察使、武康節度使。諡號“毅肅”。
