Troubleshooting Metadata Visibility of Distributed Partitioned Views
New: 5 December 2005
If a low-privilege user tries to insert, update, or delete data through a distributed partitioned view, SQL Server generates the following error:
Error 4436 "UNION ALL view '%.*ls' is not updatable because a partitioning column was not found."
This issue does not affect locally partitioned views, such as when all the underlying tables exist on the same instance of SQL Server.
For distributed queries, SQL Server must be able to read the SQL definition of CHECK constraints on the tables of the remote (linked) server. This means that the caller of a distributed query must have CONTROL, ALTER, TAKE OWNERSHIP, or VIEW DEFINITION permission on the remote table. If the caller of the distributed query does not have one of the permissions, the query fails with error 4436.
If a user does not have any one of these permissions, the value of the definition column in sys.check_constraints is NULL when the user queries the catalog.
To Resolve Error 4436
To make the definition of the CHECK constraint visible to the caller, grant the caller VIEW DEFINITION on every target table that underlies the distributed partitioned view.
For example, assume that
Server2 are federated servers and have been defined as linked servers to each other. Assume that
master.dbo.t1 is the partitioned table that can be accessed by all members of the
dpv_users database role. Assume that
dpv_users contains all users that have SELECT, INSERT, UPDATE, and DELETE access through the distributed partitioned view.
Execute the following code on each linked server.
CREATE TABLE t1(c INT PRIMARY KEY CHECK (...)) ; -- CHECK is different on each server. GO GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON t1 TO dpv_users ; GO CREATE VIEW the_dpv AS SELECT * FROM Server1.master.dbo.t1 UNION ALL SELECT * FROM Server2.master.dbo.t1 GO