Insert data into a view (SQL Server)

Question

I have the following setup:

CREATE TABLE dbo.Licenses (  Id    int IDENTITY(1,1) PRIMARY KEY,  Name  varchar(100),  RUser nvarchar(128) DEFAULT USER_NAME() )  GO  CREATE VIEW dbo.rLicenses AS SELECT Name FROM   dbo.Licenses WHERE  RUser = USER_NAME()  GO 

When I try to insert data using the view...

INSERT INTO dbo.rLicenses VALUES ('test') 

an error arises:

Cannot insert the value NULL into column Id, table master.dbo.Licenses; column does not allow nulls. INSERT fails. 

Why doesn't the auto increment of the identity column work when trying to insert using the view and how can I fix it?

Scenario is:

The different users of the database should only be able to work with their own rows in that table. Therefore I am trying to use the view as a kind of security by checking the username. Is there any better solution?

Answer 1

What about naming your column?

INSERT INTO dbo.rLicenses (name) VALUES ('test') 

It's been years since I tried updating via a view so YMMV as HLGEM mentioned.

I would consider an "INSTEAD OF" trigger on the view to allow a simple INSERT dbo.Licenses (ie the table) in the trigger