SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it. A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the table. Normally, this is when the session that created the procedure ends.
Temporary procedures named with # and ## can be created by any user. Once the procedure is created, the owner of the local procedure is the only one who can use it. Permission to execute a local temporary procedure cannot be granted to other users. If a global temporary procedure is created, all users can access it; permissions cannot be explicitly revoked. Explicitly creating a temporary procedure in tempdb (naming without a pound sign) can be performed only by those with explicit CREATE PROCEDURE permission in the tempdb database. Permission can be granted to and revoked from these tables.