SqlCommandBuilder() creates insert/update for underlying tables instead for a view

Question

I have two schemas, like this:

• Schema 'data' --> holds tables, and nobody has access to them from outside
• Schema 'ui' --> holds views which are accessibly from outside; the idea is that you can select/delete/update/insert on these views. Thus, I am doing ownership chaining.

For example:

create table data.tblTest (TestKey int not null primary key);
create view ui.vwTest as select * from data.tblTest;

Now, if I connect as a user with SQL Studio, everything is OK:

select * from ui.vwTest; -- WORKS (this is correct)
select * from data.tblTest; -- ERROR (this is correct)

insert into   ui.vwTest  (TestKey) values (17); -- WORKS (this is correct)
insert into data.tblTest (TestKey) values (17); -- ERROR (this is correct)

However, if I write a program in .NET/C# which uses SqlCommandBuilder:

SqlDataAdapter ada = new SqlDataAdapter('select * from ui.vwTest', conn);
SqlCommandBuilder b = new SqlCommandBuilder(mSQLAda);
ada.UpdateCommand = b.GetUpdateCommand();
ada.InsertCommand = b.GetInsertCommand();
ada.DeleteCommand = b.GetDeleteCommand();

==> Then in the following, the INSERT DOES NOT WORK!

[EDIT]:

The SqlCommandBuilder is analyzing the View, and instead of creating a command like

INSERT INTO ui.vwTest ...

it is creating

INSERT INTO data.tblTest ...

So in fact, the SqlCommandBuilder tries to be "intelligent" and accesses the underlying tables of the view, instead of accessing the view.

Question: Can this behaviour be changed ?

BTW, just to make it more clear, I am doing ownership chaining here.

My users have the right to see the views in schema ui, but they no rights to schema data. However, due to ownership chaning, the users can access the tables indirectly via the views in schema data.

In detail, a user is attached to a custom role, e.g. "role_user", and the role has rights to the schema, as follows:

GRANT SELECT, UPDATE, INSERT, DELETE ON SCHEMA ui TO role_user ;

but the role has NO RIGHTS on Schema 'data' !!

The nice thing of this setup is that you can apply row-level-security. With a where filter within the view, you can select only records the user is allowed to see.

As said, it works fine within the SQL window, but not with the SQLCommandBuilder. The SQLCommandBuilder analyzes the view, and tries to directly access the underlying tables, instead of accessing the view.

7 years ago, someone asked this: https://stackoverflow.com/a/320684/2504785 And his solution then was to write the SQL commands himself. But possibly, there exists now another solution? However, I found none so far ...

[/EDIT]

Answer 1

OK, the answer now definitively is:

The SqlCommandBuilder is trying to be "intelligent". If you open it with a command like SELECT * FROM vwTest, then it analyzes the view and creates commands for the underlying table, like INSERT into tblTest ...

So the problem is: SqlCommandBuilder creates commands for the underlying table, instead of for the view.

Solution:

So far, I found no way to change the behaviour of SqlCommandBuilder.

Therefore, I rewrote all the loading and updating, and I am doing now everything manually. Loading now takes place purely with SqlDataReader -- without loading into a DataTable with SqlDataAdapter. And all updating is done via creating and executing SqlCommand, without SqlCommandBuilder.

It was a lot of work, but as a reward, the application is now blazing fast. Loading is far faster than with the SqlCommandBuilder and SqlDataAdapter. Possibly I will make a benchmark comparison at some time. But when a load took 5 seconds before, it is now done "immediatedly".

Answer 2

Note this part of the ownership chaining documentation:

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated."

Therefor, in order for the ownership chaining to work properly, your table and view must be owned by the same principle.

You can change the view's owner or the table's owner by executing an ALTER AUTHORIZATION sql statement on the object.
Note that this statement only change the owner, not the schema the object belongs to. In your case, I would recommend changing the owner of the UI schema to the same owner of the Data schema, while keeping the permissions of the database principle that uses the UI schema intact.

ALTER AUTHORIZATION ON SCHEMA::UI TO <owner_of_the_data_schema>;

Note: <owner_of_the_data_schema> is a placeholder I've used since I don't know the owner name.

This way, your application user still only have access to whatever is in the ui schema, but the ownership chaining allows the objects in the ui schema to interact with the objects in the data schema.