The Covenant of Artorias is obtained by killing Great Grey Wolf Sif.
Covenant of Artorias allows the player to reach the Abyss. As such, it is necessary in order to reach the Four Kings boss battle. Without wearing the ring, it is impossible to survive the fall into the Abyss. Taking off the ring during the fight with the Four Kings will cause the player to be devoured by the Abyss. Other than this, the ring has no other purpose.
The Covenant of Artorias is one of the very few rings that do not carry over to NG+; players are required to kill Great Grey Wolf Sif every playthrough to obtain the ring and beat the game. This ring, when dropped, will end up in the chest behind Kingseeker Frampt after the player has quit and reloaded the game.
Once the player has defeated the Four Kings, the Abyss will cease killing the player in their world and the ring is no longer required to traverse it. Though, if the player is summoned as a phantom to help another player defeat the Four Kings, the ring will once again be required to traverse the Abyss.