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.