General discussion

  • Creator
    Topic
  • #2311357

    Adding records with Views

    Locked

    by blair.androschuk ·

    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?

All Comments

  • Author
    Replies
    • #3472741

      Try this !

      by raghx_2000 ·

      In reply to Adding records with Views

      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)

Viewing 0 reply threads