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. Pokémon Nut. 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