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
The post What perms does a user need to create a table variable? appeared first on .