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.
Background
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.
Note
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 Server1 and 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
See Also
Tasks
Troubleshooting Metadata Visibility
Concepts
Metadata Visibility Configuration
Creating Distributed Partitioned Views
Other Resources
CREATE VIEW (Transact-SQL)
GRANT (Transact-SQL)