ArcGIS Desktop

  • ArcGIS Pro
  • ArcMap

  • My Profile
  • Help
  • Sign Out
ArcGIS Desktop

ArcGIS Online

The mapping platform for your organization

ArcGIS Desktop

A complete professional GIS

ArcGIS Enterprise

GIS in your enterprise

ArcGIS Developers

Tools to build location-aware apps

ArcGIS Solutions

Free template maps and apps for your industry

ArcGIS Marketplace

Get apps and data for your organization

  • Documentation
  • Support
Esri
  • Sign In
user
  • My Profile
  • Sign Out

ArcMap

  • Home
  • Get Started
  • Map
  • Analyze
  • Manage Data
  • Tools
  • Extensions

Example: Create database views in SQL Server using SQL

  • Grant privileges on the tables
  • Create a view to restrict access
  • Create a view to join two tables
  • Grant privileges on the views
Complexity:
Beginner
Data Requirement:
Use your own data

You can use SQL to create a view on tables and feature classes in an enterprise geodatabase to restrict what columns or records are available to view users, or you can define a view to join information from two tables or a feature class and a table. When you include the spatial column in the view definition, view users can visualize the features in a map in an ArcGIS Desktop client.

The examples in this topic show how you can use SQL to create views created in Microsoft SQL Server database. One view restricts user access to specific columns. The other view presents content from two different tables. The examples are based on tables with the following definitions:

Table definition for employees

CREATE TABLE employees (
 emp_id integer not null,
 emp_name nvarchar(32),
 department smallint not null,
 hire_date datetime2 not null
);

Table definition for regions

CREATE TABLE regions(
 objectid integer not null,
 emp_id integer not null,
 reg_id integer not null,
 rname varchar(32),
 region geometry
);

Grant privileges on the tables

If the user creating the view is not the owner of the table or tables on which the view is based, the table owner must grant the view creator at least the privilege to select from the tables.

In this example, the tables on which the views are based (employees and regions) are owned by the user gdb. The user creating the views is user rocket.

GRANT SELECT 
 ON gdb.employees 
 TO rocket;

GRANT SELECT 
 ON gdb.regions 
 TO rocket;

Create a view to restrict access

In this example, user rocket creates a view (view_dept_201) on the employees table to restrict access to only those rows where the department is 201:

CREATE VIEW view_dept_201
 AS SELECT emp_id, name, hire_date
 FROM gdb.employees
 WHERE department = 201;

Create a view to join two tables

In this example, the view—emp_regions_view—joins a spatial table (feature class) with a nonspatial table based on the emp_id column. The view includes the ObjectID, spatial column (region), and region name (rname) from the regions feature class along with the employee name and ID from the employees table.

CREATE VIEW emp_regions_view 
 AS SELECT (e.emp_name,e.emp_id,r.objectid,r.rname,r.region)
 FROM employees e, regions r
 WHERE e.emp_id = r.emp_id;

Grant privileges on the views

You can grant privileges on the views to specific users without having to grant those users access to the base tables (employees and regions). In this example, the dispatch_mgr user is granted select privileges on both views:

GRANT SELECT
ON rocket.view_dept_201
TO dispatch_mgr;

GRANT SELECT
ON rocket.emp_regions_view
TO dispatch_mgr;

The dispatch_mgr user can now access view_dept_201 to see employee records for all department 201 employees and access emp_region_view from a database connection in ArcMap or ArcGIS Pro to see all regions. When dispatch_mgr queries a region in the view, ArcGIS returns the region name and the name and ID of all employees in that region.

ArcGIS Desktop

  • Home
  • Documentation
  • Support

ArcGIS

  • ArcGIS Online
  • ArcGIS Desktop
  • ArcGIS Enterprise
  • ArcGIS
  • ArcGIS Developer
  • ArcGIS Solutions
  • ArcGIS Marketplace

About Esri

  • About Us
  • Careers
  • Esri Blog
  • User Conference
  • Developer Summit
Esri
Tell us what you think.
Copyright © 2021 Esri. | Privacy | Legal