SQL Server – Using Temp Table in Stored Procedure for Concurrent Users

sql serverstored-procedurest-sql

I am working on a stored procedure that retrieves the objectGUID from active directory. I am storing the result in a temp table and then returning the value in an output parameter for use with other processes. The SP will be called from different stored procedures as well as web applications PHP, ASP Classic and ASP.Net.

I read HERE that (regarding temp tables):

If created inside a stored procedure they are destroyed upon completion of the stored procedure. Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another – they would remain autonomous transactions and the tables would remain autonomous objects. You may notice that my sample temporary table name started with a "#" sign.

Sounds like I am good to go but I wanted to get some advice to make sure there aren't any gotchas I am unaware of. Here is the SP.

Thanks in advance.

CREATE PROCEDURE stp_adlookup
@user varchar(100),
@objectGUID varbinary(256) OUTPUT
AS
SET NOCOUNT ON;
DECLARE @qry char(1000)
CREATE TABLE #tmp(
objectGUID nvarchar(256)
)

SET @qry = 'SELECT *
FROM openquery(ADSI, ''
SELECT  objectGUID              
FROM    ''''LDAP://mydomaincontroller.com''''
WHERE sAMAccountName = ''''' + @user + '''''
'')'
INSERT INTO #tmp
EXEC(@qry)
SELECT @objectGUID=CAST(objectGUID as varbinary(256))  FROM #tmp;
DROP TABLE #tmp
SET NOCOUNT OFF;
GO

Best Answer

Yes, each user will get their own copy of the #temp table, even if they run at the exact same time.

(However, don't use global ##temp tables, signified with two leading pound/hash signs.)

But why do you need a #temp table here at all? Something like this should work (untested, as I don't have LDAP anywhere near me):

CREATE PROCEDURE dbo.stp_adlookup -- ALWAYS use schema prefix
  @user varchar(100),
  @objectGUID varbinary(256) OUTPUT
AS
BEGIN -- use body wrappers
  SET NOCOUNT ON;

  DECLARE @qry nvarchar(max); -- don't use CHAR for dynamic SQL

  SET @qry = N'SELECT @o = objectGUID
    FROM openquery(ADSI, ''SELECT  objectGUID              
      FROM    ''''LDAP://mydomaincontroller.com''''
      WHERE sAMAccountName = ''''' + @user + ''''''')';

  -- can probably parameterize the above, but those single
  -- quotes are a nightmare. Not sure if they're necessary
  -- but I do not feel like trying to untangle them.

  EXEC sys.sp_executesql @qry, N'@o UNIQUEIDENTIFIER', @o = @objectGUID OUTPUT;

  -- SET NOCOUNT OFF; -- don't do this.
END
GO

Related Solutions

Sql-server – Stored Procedure as Create Table proxy

Yes, it should be working how you think. Below is an example proving this:

-- create the dummy login
create login TestLogin1
with password = 'p@$$w0rd';
go

use TestDB;
go

-- create the test user
create user TestUser1
for login TestLogin1;
go

-- create the proc to create a table
create procedure dbo.CreateTableProc
as

    create table dbo.SomeTestTable(id int);

go

-- give TestUser1 perms to execute CreateTableProc
grant execute
on dbo.CreateTableProc
to TestUser1;
go


-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go

exec dbo.CreateTableProc;
go

revert;
go
-- unsuccessful, permission denied

Msg 262, Level 14, State 1, Procedure CreateTableProc, Line 4
CREATE TABLE permission denied in database 'TestDB'.

-- alter the proc to execute under my security context (with CREATE TABLE perms)
alter procedure dbo.CreateTableProc
    with execute as owner
as

    create table dbo.SomeTestTable(id int);

go

-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go

exec dbo.CreateTableProc;
go

revert;
go
-- successful, table created

Command(s) completed successfully.

In this example, TestUser1 does not have the permissions to create the table. We see that when the original version of CreateTableProc is called, as the CREATE TABLE DDL executes under the security context of TestUser1. But then by modifying the CreateTableProc stored procedure definition and including the WITH EXECUTE AS OWNER clause, now TestUser1 can successfully call the proc and create a table because now the CREATE TABLE DDL executes under the security context of the owner (my database user, in the db_owner role).

Sql-server – Create Temp Table From Stored Procedure Result

See following question from stack overflow. According to this question you can use

CREATE PROCEDURE getDatabaseList
AS
BEGIN
    SELECT * FROM sys.databases
END

 SELECT * INTO #MyTempTable FROM 
OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;','EXEC getDatabaseList')

-- create temp table as usual, select , update or drop table

Best Answer

Related Solutions

Sql-server – Stored Procedure as Create Table proxy

Sql-server – Create Temp Table From Stored Procedure Result

Related Question