SQL Server – How to Update Permission for Table Variable

permissionssql serversql-server-2012update

SQL Server 2012

I have a maintenance script that I wrote for some of my operators. They are members of the fixed db_datareader and db_datawriter roles. Part of the script creates a table variable, then populates the variable with some statistics generated during the script.

The problem occurs when the script attempts to UPDATE the table variable. INSERT appears to work, but not UPDATE.

...
--!!! THIS WORKS !!!--
INSERT INTO @tblRecCounts([table_name])
select name from sys.tables where 
name not in
('sysdiagrams','sysssislog')

--!!! THIS FAILS !!!--
USE db1
UPDATE @tblRecCounts
SET f.db11PreLoadCount=dtRC.row_count
FROM @tblRecCounts f
INNER JOIN
(
<some subquery>
)...

The script works if I run it with higher permissions (e.g. owner). The error that db_datawriter gets is "The user does not have permission to perform this action."

What and how do I set permissions for these roles for a table variable that they create??? I would think the entity that creates the variable (table) would own it.

Best Answer

Martin Smith's comment ("Does the subquery itself work?") was ultimately the answer and highlighted my insufficient forensic work! ;-)

The subquery was returning ROW COUNTS from tables:

...
INNER JOIN sys.dm_db_partition_stats AS ddps 
ON i.OBJECT_ID = ddps.OBJECT_ID
...

The fixed roles I was using don't give VIEW DATABASE STATE permission (which is required to view the db_partition_stats table). So, I'll either need to mess with the roles for these users or find another workaround.

I guess it's worth noting that if you come across this circumstance, you need to deconstruct your query. The fixed db roles that I refer to CAN CREATE, INSERT, UPDATE, and DELETE records from a table variable.

Related Question