[sql-server-2008] Insert data into a view (SQL Server)

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?

INSERT INTO viewname (Column name) values (value);

You have created a table with ID as PRIMARY KEY, which satisfies UNIQUE and NOT NULL constraints, so you can't make the ID as NULL by inserting name field, so ID should also be inserted.

The error message indicates this.

You just need to specify which columns you're inserting directly into:

INSERT INTO [dbo].[rLicenses] ([Name]) VALUES ('test')

Views can be picky like that.

Looks like you are running afoul of this rule for updating views from Books Online: "INSERT statements must specify values for any columns in the underlying table that do not allow null values and have no DEFAULT definitions."

Inserting 'test' to name will lead to inserting NULL values to other columns of the base table which wont be correct as Id is a PRIMARY KEY and it cannot have NULL value.

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

What is your Compatibility Level set to? If it's 90, it's working as designed. See this article.

In any case, why not just insert directly into the table?

Go to design for that table. Now, on the right, set the ID column as the column in question. It will now auto populate without specification.