Cannot change the owner of an indexed view.

Error Message:
Msg 15219, Level 16, State 1, Line 2
Cannot change the owner of an indexed view.

Severity level:
16.

Description:
This error message appears when you try to change the owner of an indexed view.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Errors of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. You cannot change the owner of an indexed view.

Versions:
All versions of SQL Server (SQL Server 2000 and above).

Example(s):
USE Northwind;
GO

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
    ANSI_WARNINGS,
    CONCAT_NULL_YIELDS_NULL,
    ARITHABORT,
    QUOTED_IDENTIFIER,
    ANSI_NULLS ON;
GO

IF OBJECT_ID (‘dbo.MyOrderView’, ‘View’) > 0
   DROP VIEW dbo.MyOrderView;
GO
CREATE VIEW dbo.MyOrderView
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS COUNT
  FROM dbo.[Order Details] AS od
  JOIN dbo.Orders AS o
    ON od.OrderID = o.OrderID
 GROUP BY OrderDate, ProductID;
GO

CREATE UNIQUE CLUSTERED INDEX cix_MyOrderView
    ON dbo.MyOrderView (OrderDate, ProductID);
GO

ALTER AUTHORIZATION
   ON dbo.MyOrderView
   TO INFORMATION_SCHEMA

Remarks:
In the above example we first create an indexed view named MyOrderView. Then we try via ALTER AUTHORIZATION to change the owner of this view. This raises the error. As a workaround you can drop and recreate the view with a new owner.

]]>

Leave a comment

Your email address will not be published.