HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Indexed view referencing objects on two different schemas

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
objectsreferencingviewschemasindexedtwodifferent

Problem

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()

Solution

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

Code Snippets

ALTER AUTHORIZATION ON SCHEMA::sfdc TO dbo

Context

StackExchange Database Administrators Q#252263, answer score: 5

Revisions (0)

No revisions yet.