Sql-server – SQL Server 2008 – Cannot create index on Indexed View

sql-server-2008

I am attempting to create an index on a view. The view is contained in a different schema than the underlying table it's based on. When I attempt to create the view, I get the following error:

Msg 1938, Level 16, State 1, Line 1
Index cannot be created on view 'vwCdSimThk' because the underlying object 'CDSIM_Thk' has a different owner.

What I find confusing about this is that I have created this exact same view and index on at least 4 different SQL Server servers (each containing the same database schema) without getting an error like this. But on this one particular SQL Sever, I'm getting the error. Just to be clear, on all the other servers where this works, the view and underlying table it's based on are in different schema's.

Can anyone shed some light on this? Is there a server setting that needs to be changed to allow this?

Best Answer

Well apparently the owner is different.
You might have done that without even realizing it.

Try changing the owner:

ALTER AUTHORIZATION on CDSIM_Thk TO <whoever, dbo for instance>
Related Question