Software

Ask something
Discussion Locked

Adding records with Views

0 helpful
1 reply

by blair.androschuk

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I would like to add new records to a sql 2000 database with a view and without granting Select permissions to the SQL tables. Can this be done?

1 reply

by raghx_2000

According to BOL...

To create a view, the user must have CREATE VIEW permission along with SELECT permission on the tables, views, and table-valued functions being referenced in the view, and EXECUTE permission on the scalar-valued functions being invoked in the view.

--Load data into a table through a view
--The INSERT statement in this example specifies a view name; however, the new row is inserted in the view's underlying table. The order of VALUES list in the INSERT statement must match the column order of the view.

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'V1')
DROP VIEW V1
GO
CREATE TABLE T1 ( column_1 int, column_2 varchar(30))
GO
CREATE VIEW V1 AS SELECT column_2, column_1
FROM T1
GO
INSERT INTO V1
VALUES ('Row 1',1)

This discussion is closed to new replies.