Denying Access To TempDB

ian_malcolm

So before we get into this I suppose the question is “why would you even want to do this?!” I’m not advocating this idea, I’m just saying it is possible, and I suppose it prevents users from creating temp tables in tempDB and writing to disk from other databases, risking killing the server. This is just an academic exercise: A developer in the training class I recently attended actually has had their permissions to tempDB removed and so I wanted to check how to do this. Turns out it’s not that hard. See the script below for comments.

--Create a temporary principals
CREATE LOGIN tempLogin3 WITH PASSWORD = 'J345#$)thb';
GO

USE AdventureWorks2014
GO
CREATE USER tempUser3 FOR LOGIN tempLogin3;
ALTER ROLE [db_datareader] ADD MEMBER [tempUser3]

--Give IMPERSONATE permissions on user to tempUser3
--so that tempUser3 can successfully set the execution context to user2.
GRANT IMPERSONATE ON USER:: tempUser3 TO "slmnc\richard";
GO
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();

-- Set the execution context to tempLogin3.
EXECUTE AS LOGIN = 'tempLogin3';
--Verify the execution context is now tempLogin3.
SELECT SUSER_NAME(), USER_NAME();
--tempLogin3 sets the execution context to login2.
EXECUTE AS USER = 'tempUser3';
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();

CREATE TABLE #Adds ( AddID int, AddLine1 nvarchar(128) )
--DROP TABLE #Adds
INSERT INTO #Adds
SELECT AddressId, AddressLine1 from [Person].[Address]
select * from #Adds;

REVERT;
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();
REVERT;
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();

USE [tempdb]
GO
CREATE USER [tempUser3] FOR LOGIN [tempLogin3]
GO
ALTER ROLE [db_denydatareader] ADD MEMBER [tempUser3]
GO
ALTER ROLE [db_denydatawriter] ADD MEMBER [tempUser3]
GO

-- Set the execution context to tempLogin3.
EXECUTE AS LOGIN = 'tempLogin3';
--Verify the execution context is now tempLogin3.
SELECT SUSER_NAME(), USER_NAME();
--tempLogin3 sets the execution context to login2.
EXECUTE AS USER = 'tempUser3';
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();

CREATE TABLE #Adds2 ( AddID int, AddLine2 nvarchar(1024) )
--DROP TABLE #Adds2
INSERT INTO #Adds2
SELECT AddressId, AddressLine2 from Adventureworks2014.[Person].[Address]

REVERT;
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();
REVERT;
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();

use [tempdb]
GO
ALTER ROLE [db_denydatareader] DROP MEMBER [tempUser3]
GO
ALTER ROLE [db_denydatawriter] DROP MEMBER [tempUser3]
GO
DENY CONNECT TO [tempUser3]
GO

Use AdventureWorks2014
GO
-- Set the execution context to tempLogin3.
EXECUTE AS LOGIN = 'tempLogin3';
--Verify the execution context is now tempLogin3.
SELECT SUSER_NAME(), USER_NAME();
--tempLogin3 sets the execution context to login2.
EXECUTE AS USER = 'tempUser3';
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();

CREATE TABLE #Adds3 ( AddID int, AddLine2 nvarchar(1024) )
--DROP TABLE #Adds3
INSERT INTO #Adds3
SELECT AddressId, AddressLine2 from [Person].[Address]

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s