ArcGIS for Desktop

  • Documentation
  • Pricing
  • Support

  • My Profile
  • Help
  • Sign Out
ArcGIS for Desktop

ArcGIS Online

The mapping platform for your organization

ArcGIS for Desktop

A complete professional GIS

ArcGIS for Server

GIS in your enterprise

ArcGIS for 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
  • Pricing
  • Support
Esri
  • Sign In
user
  • My Profile
  • Sign Out

Help

  • Home
  • Get Started
  • Map
  • Analyze
  • Manage Data
  • Tools
  • More...

Creating the administrator users and connection in SQL Server for the BIS workspace

  • Creating the administrator geodatabase users
  • Creating the administrator database connection

Creating the administrator geodatabase users

You can choose to add users to an enterprise geodatabase in SQL Server through Microsoft SQL Server Management Studio. The geodatabase users are underlying enterprise database users and store the BIS feature classes and tables. You need to grant the appropriate permissions.

For BIS workspace administrators, the following is suggested:

User TypeRoleSQL Server Permissions

Data Creator

CONNECT

CREATE TABLE

CREATE VIEW

CREATE PROCEDURE

Administrator permissions

The following scripts can be used in Microsoft SQL Server Management Studio to create a new database user to store the BIS feature classes and tables, and grant the appropriate permissions.

Copying and pasting the examples may cause syntax errors.

  1. Create user and schema.
    USE BISDB
    GO
    EXEC sp_addlogin N'bis', 'bis', @logindb, @loginlang
    GO 
    CREATE USER [bis] FOR LOGIN [bis]
    GO
    CREATE SCHEMA [bis] AUTHORIZATION [bis]
    GO
    ALTER USER [bis] WITH DEFAULT_SCHEMA=[bis]
    GO
    
  2. Grant privileges.
    USE BISDB
    GO
    EXEC sp_droprolemember 'gis_data_creator', 'bis'
    GO
    EXEC sp_droprole 'gis_data_creator'
    GO
    CREATE ROLE gis_data_creator AUTHORIZATION dbo
    GO
    GRANT CREATE TABLE TO gis_data_creator
    GO
    GRANT CREATE PROCEDURE TO gis_data_creator
    GO
    GRANT CREATE VIEW TO gis_data_creator
    GO
    EXEC sp_addrolemember 'gis_data_creator', 'bis'
    GO
    
  3. Verify roles.
    EXEC sp_helprolemember 'gis_data_creator'
    GO
    
  4. Verify role permissions.
    select dp.NAME AS principal_name,
     dp.type_desc AS principal_type_desc,
     o.NAME AS object_name,
     p.permission_name,
     p.state_desc AS permission_state_desc 
     from sys.database_permissions p
     left OUTER JOIN sys.all_objects o
     on p.major_id = o.OBJECT_ID
     inner JOIN sys.database_principals dp
     on p.grantee_principal_id = dp.principal_id
     where dp.NAME = 'gis_data_creator'
    GO
    
  5. Verify user permissions.
    select USER_NAME(p.grantee_principal_id) AS principal_name,
     dp.type_desc AS principal_type_desc,
     p.class_desc,
     OBJECT_NAME(p.major_id) AS object_name,
     p.permission_name,
     p.state_desc AS permission_state_desc 
    from sys.database_permissions p
    inner JOIN sys.database_principals dp
    on p.grantee_principal_id = dp.principal_id
    where USER_NAME(p.grantee_principal_id) = 'bis'
    
  6. Associate Login bis with User bis.
    USE BISDB
    GO
    EXEC sp_change_users_login 'update_one','bis','bis'
    GO
    EXEC sp_helpuser 'bis'
    

Creating the administrator database connection

You need to create an administrator database connection when the BIS workspace is stored in an enterprise geodatabase. Create a database connection in ArcCatalog with the BIS user; this will be the BIS workspace location.

See Database connections in ArcGIS for Desktop.

Feedback on this topic?

ArcGIS for Desktop

  • Home
  • Documentation
  • Pricing
  • Support

ArcGIS Platform

  • ArcGIS Online
  • ArcGIS for Desktop
  • ArcGIS for Server
  • ArcGIS for Developers
  • ArcGIS Solutions
  • ArcGIS Marketplace

About Esri

  • About Us
  • Careers
  • Insiders Blog
  • User Conference
  • Developer Summit
Esri
© Copyright 2016 Environmental Systems Research Institute, Inc. | Privacy | Legal