SQL Interview: 1: db_datareader and temporary tables

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Security Level: Medium

Question:

I have an application that connects to the database as a user called ReaderApp. The only role that ReaderApp has been added to is db_datareader. The application was previously only using SELECT statements to read data that was required.

For performance reasons, the development team now wants the application to use temporary tables in their queries. They need to be able to create temporary tables, insert data into them, read the data back from them, and drop them..

What additional permissions (if any) need to be assigned to ReaderApp, or which additional roles (if any) should ReaderApp be assigned to?

Answer:

None. All users who have access to the database have permission to work with temporary tables.

2021-03-19