Pretty much every T-SQL programmer knows about temp tables very early in their journey with the language. They are generally wonderful tools that, when used properly, can be invaluable for storing data for a short amount of time. When used improperly they can be somewhat problematic, but certainly they are a tool we all use regularly.

But did you realize you can create a temporary stored procedure as well? It is not something I had ever used before, and while I remember hearing they existed a few times over the years, I had never tried them. The other day. I was creating an informal demo of some data, and once I had written the main query that listed rows that needed to be looked at, I needed a way to display the details of some rows.

So, on another connection I started writing some code that would let me look at the details for the listed row. I ended up needing 7 queries to display the results I wanted. More or less a sub report. It was something that I could easily just paste in a key value, so that was okay. But then I needed this on multiple connections, and I really wanted to be able to make a script that I could include in my main query and just copy, paste, and execute.

Then I remembered temporary stored procedures, and figured I would give it a try. It was a good choice.

Quick Intro to Temporary Stored Procedures

A temporary stored procedure is at its core, the same as a regular stored procedure. Just a batch of SQL statements or commands that you can create and use together as a unit. There are a few differences important and essential between a temporary stored procedure and a regular one.

1. Lifecycle – They only exist as long as they are being referenced. When you end the connection they were created on, they go away.
2. Scope – There are two kinds: Local (prefixed with #) are only accessible from the one connection. Global (prefixed with ##) are available from any connection, and by other users. Global temp objects all share a common namespace.
3. Access – While their code behaves like they are created in the database you create them in, they are accessible from any database context.

Just like with temporary tables, local temporary stored procedures are pretty safe to use in any context. While global temporary procedures do enable some slight security concerns, they are slight and they do have some nice properties when needed.

Examples

For my examples I am going to create a scenario where I want to query of all the tables in a database and then look at their columns and constraints seperately. Then I want to get a report of the columns and constraints for the object.

I am going to use the [AdventureWorksDW2022 database](AdventureWorksDW2022 database) for this, but the database isn’t really that important for this demo. You can create this in any database, even TempDB.

USE AdventureWorksDW2022;

--Top 5 since seeing specific objects isn't that interesting
SELECT TOP 5 OBJECT_SCHEMA_NAME(object_id) as schema_name,
       name AS table_name, object_id
FROM sys.tables
WHERE name like 'Fact%'
ORDER BY name DESC;

This returns:

schema_name   table_name                object_id
------------- ------------------------- -----------
dbo           FactSurveyResponse        1397580017
dbo           FactSalesQuota            1381579960
dbo           FactResellerSales         1365579903
dbo           FactProductInventory      1349579846
dbo           FactInternetSalesReason   1333579789

Editor note: Wondering why the query is sorted descending? This was because the first name in ascending order was FactAdditionalInternationalProductDescription and it was harder to format and not have scroll bars on the page!

Now I want to look at the columns of the table and the foreign key constraints too. I am going to code this as a temporary stored procedure. You can see in the CREATE PROCEDURE statement that I am prefixing the procedure name with #:

CREATE OR ALTER PROCEDURE #TableConstraints
   @object_id int
AS
BEGIN
SELECT 'table',name
from   sys.objects
where  object_id = @object_id
   --just very simple column output for this demo, but 
   --the true story had a lot more columns and more joins
   --involved for the queries I was working with.
   SELECT 'columns', name as column_name
   FROM   sys.columns
   WHERE  object_id = @object_id
   ORDER  BY column_id;

   SELECT 'FK', name as constraint_name
   FROM   sys.foreign_keys
   WHERE  parent_object_id = @object_id
      or  referenced_object_id = @object_id
   ORDER  BY name;
END;

Using the output of the first query, I could grab an object_id value and pass it into this procedure, but instead, I will expand the columns of query to build that query for me with dynamic sql.

SELECT TOP 5 OBJECT_SCHEMA_NAME(object_id) as schema_name, name AS table_name,
   CONCAT('EXEC #TableConstraints @object_id = ',object_id,';') AS test_query
FROM sys.tables
WHERE name like 'Fact%'
ORDER BY name DESC;

Execute this query, and you will see something like the following.

schema_name  table_name                test_query
------------ ------------------------- -------------------------------------------------
dbo          FactSurveyResponse        EXEC #TableConstraints @object_id = 1397580017;
dbo          FactSalesQuota            EXEC #TableConstraints @object_id = 1381579960;
dbo          FactResellerSales         EXEC #TableConstraints @object_id = 1365579903;
dbo          FactProductInventory      EXEC #TableConstraints @object_id = 1349579846;
dbo          FactInternetSalesReason   EXEC #TableConstraints @object_id = 1333579789;

Grab one of the values from the test_query column and execute it:

EXEC #TableConstraints @object_id = 1381579960;

The output will look like this:

      name
----- -----------------------
table FactSalesQuota

        column_name
------- -----------------
columns SalesQuotaKey
columns EmployeeKey
columns DateKey
columns CalendarYear
columns CalendarQuarter
columns SalesAmountQuota
columns Date

     constraint_name
---- --------------------------------
FK   FK_FactSalesQuota_DimDate
FK   FK_FactSalesQuota_DimEmployee

And you can look at the rest of the objects by grabbing all of the calls and executing them one by one, much like you could with a drill in report..

Database context

Now, just to show how it works with other databases, I will change database context to tempdb. Execute the following:

USE Tempdb
GO
SELECT DB_NAME();

EXEC #TableConstraints @object_id = 1381579960;
GO

You’ll notice that your output is the same as before, with the addition of a line that looks like this:

--------------------
tempdb

This can be really handy when you are working on a query that needs more than one database and you don’t want to keep switching back and forth.

Local vs Global

In the current setup, I can execute the detail procedure (#TableConstraints) only in the same connection as I am working in. But what if I want to execute it on another connection (like in this case, I don’t want to lose my results, so I can output more than one set of results for comparison.

For this we can make it a global object. If I change the procedure to a global temporary procedure:

DROP PROCEDURE IF EXISTS #TableConstraints; --local version 
GO
CREATE OR ALTER PROCEDURE ##TableConstraints 
   @object_id int
AS
BEGIN
SELECT 'table',name
FROM   sys.objects
WHERE  object_id = @object_id
   --just very simple column output for this demo, but 
   --the true story had a lot more columns and more joins
   --involved for the queries I was working with.
   SELECT 'columns', name as column_name
   FROM   sys.columns
   WHERE  object_id = @object_id
   ORDER  BY column_id;

   SELECT 'FK', name as constraint_name
   FROM   sys.foreign_keys
   WHERE  parent_object_id = @object_id
      OR  referenced_object_id = @object_id
   ORDER  BY name 
END;

Beware that global temporary object names must be unique amongst all connections due to how they are accessible throughout the server.

Now you can check the structure information of a table in the AdventureWorksDW2022 database from any database and any connection, as long as the original connection is open (you will need to change the code in the SELECT query to return ## instead of #. Quick demo of the lifecycle in the next subsection.)

This is really great, as now I can execute two different calls, in two more connections, and perhaps put them up, side by side, or use the ability to compare files in SSMS 21.

How do these objects go away?

They technically go away when there are no references left. For example, let’s take this procedure, which takes a parameter that sets a @DelayTime to tell it how long to execute, formatted for the WAITFOR command:

CREATE PROCEDURE ##Test
  @DelayTime varchar(10) = '00:00:30'
AS
  PRINT 'Waiting ' + @delayTime;
  RAISERROR ('Starting ##Test',10,1 ) WITH NOWAIT;
  WAITFOR DELAY @DelayTime;
  RAISERROR ('Finished ##Test',10,1 ) WITH NOWAIT;
GO

This procedure will put out a few messages to let us know it is executing, then end.

Note: RAISERROR with a 10 severity using NOWAIT is similar to PRINT, but it doesn’t wait for other operations to complete like PRINT does. It does its communication, so you can see it immediately along with prior PRINT output. It is a good trick to flush the cache when you are trying to see that your code is running.

Setting up a lifecycle test rig

On a different connection (or 2), go out and execute ##Test. By default, it will wait 30 seconds, then end. Before they complete, either execute the following command on any connection:

DROP PROCEDURE ##Test;
GO

Or disconnect the connection that created the procedure. In the connections you executed ##Test, you will see this:

Waiting 00:00:30
Starting ##Test
Finished ##Test

Any instances you started before the drop/connect will continue to execute. But, you will not be able to start new instances even when others are still executing:

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##Test'.

And of course, if you close the connection in general, the procedure goes away when other connections aren’t in the middle of using it.

What about accessing other temp objects?

From a local temporary stored procedure, this is perfectly fine. But what about a global one? Lets say I am in my database. I create a permanent table named TestGlobalJoin, and a temporary table named #Test.

USE TestAccess
GO
CREATE TABLE TestGlobalJoin
(
TestId int
)
INSERT INTO TestGlobalJoin
VALUES (1);

CREATE TABLE #test
(
TestId int
)
INSERT INTO #test
VALUES (1);
GO

Now, I am doing some repeated work with that table, and I create a global temporary procedure.

CREATE PROCEDURE ##testQuery
AS
SELECT *
FROM   TestGlobalJoin AS TGJ
        JOIN #test AS T
          ON T.testId = TGJ.TestId;
GO

Executing this code:

EXEC ##testQuery;

You see the following:

TestId      TestId
----------- -----------
1           1

Now, I change databases to TempDb and execute the procedure.

USE TempDb;

EXECUTE ##testQuery;

Executing this table in the context of TempDB, you get the same results as before, two columns both named TestId. Now lets get interesting, still in the context of TempDB:

DROP TABLE #test;
EXEC ##testQuery;

Logically, you get this error:

Msg 208, Level 16, State 0, Procedure ##testQuery, Line 3 [Batch Start Line 16]
Invalid object name '#test'.

But what if we recreate the temp table, now in a different database context?

CREATE TABLE #Test
(
  TestId int,
  NewColumn int
);
INSERT INTO #Test
VALUES (1,1);

Exec ##TestQuery

We get the new column, so our query still joins to the TestAccess table, but uses the new temp table:

TestId      TestId      NewColumn
=---------- ----------- -----------
1           1           1

Cool. though it could get complicated if you aren’t careful!

Security Concerns

The biggest concern is that you cannot restrict the users of a global temporary stored procedures (or tables). They are created in the context of the user that creates them, but every user in the system can access them too. However, security is not transferred in the same way as a normal stored procedure, so the risk is minimal.

As an example, let’s create a few logins and users, and a table of data that one of the users cannot access.

--create a new database
CREATE DATABASE TestAccess;

--make sure you are in the context of that database
USE TestAccess;

--create logines for the test cases
CREATE LOGIN denied WITH PASSWORD = 'aslfjqwo fqwoqjow23#';
CREATE LOGIN allowed WITH PASSWORD = '##$Er432-034344w256$';
CREATE LOGIN cantAccessDatabase WITH PASSWORD = '43q4p(()uJJ#09392$';

--grant the first two accounts access to the database
CREATE USER denied FROM LOGIN denied;
CREATE USER allowed FROM LOGIN allowed;

--create a table with one row of data:
CREATE TABLE TestAccess
(
   testAccessId int
);
INSERT INTO TestAccess
VALUES(1);

--apply security to each user.
GRANT SELECT ON TestAccess TO ALLOWED;
DENY SELECT ON TestAccess TO denied;
GO

--Finally, create a temporary and regular stored
--procedure that queries the table:

CREATE PROCEDURE ##TestAccess
AS
  SELECT * FROM testAccess;
GO

CREATE PROCEDURE TestAccess_Regular
AS
  SELECT * FROM testAccess;
GO

--grant execute rights to our users.
GRANT EXECUTE ON TestAccess_Regular to allowed, denied;
GO

Note that you can’t grant access to a user in this database to execute the temporary stored procedure (which would also mean you can’t deny them either.)

GRANT EXECUTE ON ##TestAccess to allowed, denied;

This returns:

Msg 4610, Level 16, State 1, Line 30
You can only grant or revoke permissions on objects in the current database.

Running the Security Tests:

For each block of code, I will impersonate a user, then try to select data from the table, run the standard stored procedure, then the global temporary stored procedure.

Note: Be careful with your context. It is easy to get lost if you execute EXECUTE AS multiple times.

EXECUTE AS user = 'allowed';
GO
SELECT CAST(SUSER_SNAME() AS nvarchar(25)) AS system_name,
       *
FROM   testAccess;
GO
EXECUTE TestAccess_Regular;
GO
EXECUTE ##TestAccess;
GO
REVERT;

This should return:

system_name               testAccessId
------------------------- ------------
allowed                   1

testAccessId
------------
1

testAccessId
------------
1

This works as we set it up. All executions worked, great. Next up, the denied user:

EXECUTE AS user = 'denied';
GO
SELECT CAST(SUSER_SNAME() AS nvarchar(25)) AS system_name,
       * 
FROM testAccess;
GO
EXECUTE TestAccess_Regular;
GO
EXECUTE ##TestAccess;
GO
REVERT;

This returns the following three outputs:

Msg 229, Level 14, State 5, Line 46
The SELECT permission was denied on the object 'testAccess', 
database 'TestAccess', schema 'dbo'.

testAccessId
=-----------
1

Msg 229, Level 14, State 5, Procedure ##TestAccess, Line 3 [Batch Start Line 48]
The SELECT permission was denied on the object 'testAccess', 
database 'TestAccess', schema 'dbo'.

You can see now that the user is denied access from the query, and from the global temporary stored procedure. But since they were given rights to the stored procedure, they can see the data in the table because of ownership chaining.

Lastly, lets see what happens when we access the global temporary stored proceudre as a user that cannot access the database.

When it comes to the user that doesn’t have access to the database, the output probably seems obvious (especially since the local user failed), but it is worth trying just to make sure:

USE TempDb;

EXECUTE AS login = 'cantAccessDatabase'

SELECT CAST(SUSER_SNAME() AS nvarchar(25)) AS system_name,
       * 
FROM TestAccess.dbo.testAccess;
GO
EXECUTE TestAccess.dbo.TestAccess_Regular;
GO
EXECUTE ##TestAccess;
GO
REVERT;
GO

The only difference in the output is that the error for the ##TestAccess is different from the other two error messages:

Msg 916, Level 14, State 2, Line 64
The server principal "cantAccessDatabase" is not able to access the database 
"TestAccess" under the current security context.

Msg 916, Level 14, State 2, Line 90
The server principal "cantAccessDatabase" is not able to access the database 
"TestAccess" under the current security context.

Msg 972, Level 17, State 1, Line 67
Could not use database '12' during procedure execution.

What about procedure impersonation

One last test, because this one worried me the most. In a stored procedure (including a temporary one), you can include an EXECUTE AS to the declaration that will give you access to an elevated permissions for some one-time task. At the highest level of impersonation, a database owner can create a procedure with an EXECUTE AS the dbo user in the database.

CREATE PROCEDURE ##TestAccess_dbo
WITH EXECUTE AS LOGIN  'sa'
AS
SELECT * FROM testAccess;
GO

Thankfully this will not work, even as the user who owns the database:

SELECT SUSER_SNAME();
EXECUTE ##TestAccess_dbo;

This returns:

=-----------------------
TEST-SQLSERVER\drsql

Msg 916, Level 14, State 2, Procedure ##TestAccess_dbo, Line 4 
[Batch Start Line 239]
The server principal "sa" is not able to access the database "TestAccess" 
under the current security context.

That is my login that I used to log in, and is the owner of the database:

SELECT db.name, sp.name
FROM   sys.databases as db
         JOIN sys.server_principals AS sp
            ON db.owner_sid =  sp.sid
WHERE  db.name = 'TestAccess';

This returns:

name           name
-------------- ----------------------------
TestAccess     TEST-SQLSERVER\drsql

So while the temporary stored procedure lets the user execute the code that has been compiled into the temporary stored procedure, it does not give them any real access.

What about the code?

This is the only serious security issue that i could find that was seemingly an issue. Any of the users I tested with could see the definition of the procedure.

EXECUTE AS user = 'denied';
GO
USE tempdb
GO
SELECT SUSER_SNAME()
SELECT OBJECT_DEFINITION(OBJECT_ID('tempdb..##TestAccess'));
GO
USE TestAccess;
GO
REVERT;

You can see from the output, that even though this user is denied access to the database, it does have access to the code.

-------------------
denied

--------------------------------
CREATE PROCEDURE ##TestAccess
AS
SELECT * FROM testAccess;

What if the user can’t even access the database? Change the EXECUTE AS to impersonate the cantAccessDatabase login:

USE TempDb;
EXECUTE AS login = 'cantAccessDatabase'

It still will allow you to read the code:

---------------------------------
cantAccessDatabase

---------------------------------
CREATE PROCEDURE ##TestAccess
AS
SELECT * FROM testAccess

This is a security hole, if perhaps a rather small one. The most important thing to understand is that you would not want to put any information that you consider “private” in a temporary object of any type.

The same would be true for a global temporary table.

Summary

While temporary tables are known and used by almost every T-SQL programmer, temporary stored procedures are definitely not a commonly used (or at least not commonly discussed) type of object. In this article I showed one use case for use when you are running parameterized scripts to view some data. The fact that they can execute from any database context but work as if there are in the database where they were created makes them great for that type of usage.

I might even start using them more for things like utility queries. Either way, I am glad I took the time to learn them, and to make sure I understood the way they worked with security, with the only major security concerns being that the code you use for them will be accessible to anyone who can access TempDb, which should be everyone.