What is it?

SP_ indicates a "System Procedure".  Any user with enough rights can create a system procedure.

What does it do?

They provide global scope to a Stored Procedure.  A procedure is normally only visible when it exists in the currently scoped database (the database you are connected to).  Alternatively procedures can be launched from another database using the standard naming convention: [DatabaseName].[Owner].[ProcedureName]

The SP_ prefix allows a procedure to be called from any database without the procedure existing in the database and without requiring the "DatabaseName" component of the naming convention.

So, in short, when an SP_ prefixed procedure is placed into the Master database it enables a procedure to be called from any other database.  For instance, users can be added/removed and passwords updated while "connected" to any specific database.  The user management stored procedures don't need to be added to every database, so they are shared by putting them into the master database AND prefixing with SP_

Tips:

Don't ever create SP_ procedures in any database other than Master.  It's possible to do this but that just confuses things when you try to find it to update it.

Ensure that users have the access rights to use SP_ procedures if they need to.  That is, DO NOT give permission to the Master database, but instead search for the specific procedure and tick on the ability to execute the procedure.  Be careful when making future adjustments to use "Alter Procedure" instead of a drop and create as alter maintains permissions whereas drop and create looses all permissions.

SP_ procedures are not cached.  That is, a normal stored procedure runs faster after the second or third execution because it's execution plan is cached and optimised on every execution, but SP_ procedures are never optimised and run for the "first time" every time.  Therefore, only use for procedures that are likely to be used infrequently, such as logging errors.

ONLY use for procedures that can be used from any database.  If the procedure is not applicable to "any situation" or "any database", then don't put it into the Master with an SP_ prefix.