4

When I try to create/alter a view to create an index like this

CREATE UNIQUE CLUSTERED INDEX IDX_vSalPopulation
   ON sfdc.vSalPopulation (ID);

I get the following error message

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

When I check the tables I see that the tables are owned by different schemas

exec sp_tables 'dbo.YR_TRM_SBTRM_TABLE'
exec sp_tables 'vSalPopulation'
TABLE_QUALIFIER     TABLE_OWNER       TABLE_NAME              TABLE_TYPE    REMARKS
MyDB                dbo               YR_TRM_SBTRM_TABLE      TABLE         NULL 
MyDB                sfdc              vSalPopulation          VIEW          NULL

The documentation on indexed views states that you cannot have an indexed view that references two different databases.

  • The view must be created by using the WITH SCHEMABINDING option.
  • The view must reference only base tables that are in the same database as the view.
  • The view cannot reference other views. … etc

However, I have the same database but two different schemas. Maybe the problem is actually the third requirement? While I am not referencing other views, there are functions. Maybe I misunderstand the error message. Permissions? So, generally, is it possible to have an indexed view that references objects from two different schemas?

A simplified definition of the view that gives me the same error looks like this

ALTER VIEW sfdc.vSalPopulation
   WITH SCHEMABINDING 
AS
SELECT DISTINCT
    ID
FROM dbo.CAN
INNER JOIN dbo.YR_TRM_SBTRM_TABLE YTS ON CAN.YR_CDE = YTS.YR_CDE
WHERE YTS.SBTRM_END_DTE > GETDATE()

1 Answer 1

6

I think I found the answer here. Basically, grant authorization on my second schema to dbo:

ALTER AUTHORIZATION ON SCHEMA::sfdc TO dbo

So, it really has to do with ownership/authorization rather than the schema itself. See: https://www.sqlteam.com/articles/understanding-the-difference-between-owners-and-schemas-in-sql-server

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.