I wanted to test access on a table "Items" through a view that would implement multi-tenant operation where the code is currently single tenant.

So I renamed Items to _Items and set about to create a view called Items. If I do a simple SELECT * FROM _Items it works and the code is none the wiser. I then tried to join the items table with the users table so I could test multi-tenant access though the view, but am getting an error that I just don't understand.

Here is the query:

CREATE VIEW [dbo].[Items] AS 
SELECT * FROM _Items I INNER JOIN Users U
    ON I.TenantId = U.TenantId
WHERE U.UserName = SUSER_SNAME()

The asterisk in SELECT * FROM is flagged and hovering over it produces: SQL71508 :: The model already has an element that has the same name dbo.Items.Id.

Clicking Update in VS2017 produces the same error for each column.

SQL71508 :: The model already has an element that has the same name dbo.Items.Id.
SQL71508 :: The model already has an element that has the same name dbo.Items.Id.
SQL71508 :: The model already has an element that has the same name dbo.Items.Active.
SQL71508 :: The model already has an element that has the same name dbo.Items.TenantId.
SQL71508 :: The model already has an element that has the same name dbo.Items.Active.
SQL71508 :: The model already has an element that has the same name dbo.Items.TenantId.

What am I doing wrong!

TIA

CodePudding user response：

If you just want your view to have the columns from _Items, filtered by Users, try

CREATE OR ALTER VIEW [dbo].[Items] AS 
SELECT I.* FROM _Items I INNER JOIN Users U
    ON I.TenantId = U.TenantId
WHERE U.UserName = SUSER_SNAME()

Your SQL would grab all columns from both Users and _Items, and you definitely have at least one column with the same name between them, that being TennantId, the column you're joining on.

CodePudding user response：

It seems the VIEW you're trying to create already exists. Use CREATE OR ALTER VIEW instead of CREATE VIEW to overwrite it.