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

• vikas.chaudhary

  SSC-Addicted

  Points: 440

  More actions

  September 9, 2013 at 5:34 pm

  #282166

  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

• Dennis Post

  SSCarpal Tunnel

  Points: 4473

  More actions

  September 10, 2013 at 5:05 am

  #1648801

  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

• Dennis Post

  SSCarpal Tunnel

  Points: 4473

  More actions

  September 10, 2013 at 6:42 am

  #1648838

  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

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  September 10, 2013 at 4:14 pm

  #1649053

  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]