Denying Access To TempDB
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]