In the DECLARE CURSOR statement, you can specify the scope of the cursor after its name. The default scope is GLOBAL, but you can change the default scope, changing the database option default to local cursor.
You should not rely on the default cursor scope of SQL Server. It is recommended that you declare the cursor explicitly as either LOCAL or GLOBAL, because the default cursor scope might change in future versions of SQL Server.
You can use a global cursor anywhere in the same connection in which the cursor was created, whereas local cursors are valid only within the scope of the batch, procedure, user-defined function, or trigger where the cursor is created. The cursor is automatically deallocated when it goes out ...