Index cannot be created on view because the underlying object has a different owner

  • Hello Guys,

    We are getting the below error while creating the clustered index on the index view:

    "Index cannot be created on view because the underlying object has a different owner"

    The schemas of the "base table" referred in the view and actual schema of the "view" are owned by different database roles and that's why we are getting this error.

    As a work-around, I am thinking of making "dbo" user as the owner of both the schemas (base table and indexed view schema) and then grant full permission to database roles of "base table" and "indexed view" on their respective schemas

    Question: Does this approach has any security concerns when all the schemas will be owned by dbo user ?

    Thanks in advance,

    Vikas

  • Set it up in development.

    Make the changes.

    Try your best to take advantage of the new rights.



    For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

  • Here's a script to reproduce the error and correct it.

    CREATE SCHEMA Test1 AUTHORIZATION Guest

    GO

    CREATE SCHEMA Test2 AUTHORIZATION dbo

    GO

    IF OBJECT_ID('Test1.Tmp1') IS NOT NULL

    DROP TABLE Test1.Tmp1

    CREATE TABLE Test1.Tmp1

    (

    ID INT IDENTITY,

    CharVal Char(1),

    )

    IF OBJECT_ID('Test1.Tmp2') IS NOT NULL

    DROP TABLE Test2.Tmp2

    CREATE TABLE Test2.Tmp2

    (

    ID2 INT IDENTITY,

    CharVal2 Char(1)

    )

    INSERTTest1.Tmp1

    SELECTTOP 100 CHAR(ABS(CHECKSUM(NEWID()) % 26) +97)

    FROMsys.all_columns c1, sys.all_columns c2

    INSERTTest2.Tmp2

    SELECTTOP 100 CHAR(ABS(CHECKSUM(NEWID()) % 26) +97)

    FROMsys.all_columns c1, sys.all_columns c2

    GO

    -- DROP VIEW Test1.View1

    CREATE VIEW Test1.View1

    WITH SCHEMABINDING

    AS

    (

    SELECTT1.ID, T1.CharVal, T2.CharVal2

    FROMTest1.Tmp1 T1

    JOIN Test2.Tmp2 T2

    ON T1.ID = T2.ID2

    )

    GO

    CREATE UNIQUE CLUSTERED INDEX UCI_TmpTest ON Test1.View1 (ID, CharVal, CharVal2)

    --Generates error

    --Msg 1938, Level 16, State 1, Line 1

    --Index cannot be created on view 'View1' because the underlying object 'Tmp2' has a different owner.

    GO

    ALTER AUTHORIZATION ON SCHEMA::Test1 TO dbo

    GO

    -- Try creating the unique clustered index again

    CREATE UNIQUE CLUSTERED INDEX UCI_TmpTest ON Test1.View1 (ID, CharVal, CharVal2)

    GO

    DROP VIEW Test1.View1

    DROP TABLE Test1.Tmp1

    DROP TABLE Test2.Tmp2

    DROP SCHEMA Test1

    DROP SCHEMA Test2

    Make sure you make a reversing script just in case.



    For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

  • It is difficult to answer the question, because I don't know why you set up different schema owners in the first place. My preference is to have dbo to own everything, but that also requires the right way of thinking about security for that model.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply