What perms does a user need to create a table variable?

This came up because a user had db_owner perms and it was only reading from a database and creating a table variable from it.

I created a new user (on a test system) with read only permissions to test if the code would still work. This is using the AdventureWorks2019 sample db: https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms

DECLARE @department TABLE (id smallint)
INSERT INTO @department (Id)
SELECT [DepartmentID]
FROM [AdventureWorks2017].[HumanResources].[Department]
DELETE from @department where Id in
(
SELECT [DepartmentID]
FROM [AdventureWorks2017].[HumanResources].[Department]
where DepartmentID > 7
)
SELECT distinct s.id, c.[name], c.groupname, c.modifieddate
FROM @department s
join [HumanResources].[Department] c on s.Id = c.DepartmentID 

This table variable was created successfully and read was all that was needed to the appropriate user database or objects in the database.

For more information on table variables, visit https://docs.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql?view=sql-server-ver15

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.