Add logins and users to SQL Server—ArcGIS AllSource

In Microsoft SQL Server, database administrators add logins to the SQL Server instance, and these logins are mapped to users in individual databases on the SQL Server instance. Database users who will create tables and feature classes must have privileges necessary to create these objects in the database, and they must have a schema in which they can create them. When using ArcGIS, that schema must have the same name as the database user.

You can use the Create Database User geoprocessing tool or a Python script that calls the management.CreateDatabaseUser ArcPy function to do all of the following:

Create or add a login to the SQL Server instance.
Create a user mapped to the specified login.
Create a matching schema for the user in the database you specify.
Grant privileges to the user sufficient to create tables, feature classes, and views in the specified database.

Add a user who can create data

You can run the Create Database User tool from ArcGIS AllSource or call the management.CreateDatabaseUser ArcPy function in a Python script to create a database user who can create tables, feature classes, and views.

You must be connected to the database with a login that has sysadmin privileges to the SQL Server instance to run the Create Database User tool or management.CreateDatabaseUser function.

To create a database user for a Microsoft Windows login, the login must exist before you run the tool or function.

Use the Create Database User tool

Follow these steps to create a database user in SQL Server using the Create Database User tool:

Start ArcGIS AllSource.
Connect to the database or geodatabase with a login that has sysadmin privileges in the SQL Server instance.
Open the Create Database User tool.
The tool is in the Geodatabase Administration toolset of the Data Management toolbox.
Specify the database connection for the Input Database Connection.
Choose whether you want to create a SQL Server-authenticated login or use an existing Windows-authenticated login.
- Leave Create Operating System Authenticated User unchecked to create a SQL Server-authenticated login. Note that, by default, SQL Server instances use Windows authentication only. If your instance is not configured to use SQL Server and Windows authentication, choosing to create a database-authenticated login will fail.
- Check Create Operating System Authenticated User to use an existing Windows-authenticated login.
Type a name for the database user that the tool will create.
If you choose to create a SQL Server-authenticated login, the name you type here will also be used for the login.
Type a password for the database user.
If you already have a database role that you want to add this user to, specify the role.
Click Run.

Run a Python script

To script user creation, follow these steps:

Create a text file on an ArcGIS client machine and copy one of the following blocks of code into the file.

Alter the code to use information specific to your site

The examples assume you have an existing database connection file that connects as a user who has sysadmin privileges in the SQL Server instance. If you do not have a connection file, create one before you run the script.

# Name: createdatabaseuser_exampleW.py
# Description: Uses existing database connection file
# on a Windows computer to create a database user in SQL Server.

# Import arcpy module
import arcpy
 
# Provide a database connection file for the system administrator user.
connection = "C:\\ArcGIS\connection_files\<Connection file>.sde"

# Process: Create database user that can create data for an existing operating system login.
arcpy.CreateDatabaseUser_management(connection, "OPERATING_SYSTEM_USER", "NETWORKNAME\\username")

# Name: createdatabaseuser_exampleL.py
# Description: Uses existing database connection file
# on a Linux computer to create a database user in SQL Server.

# Import arcpy module
import arcpy
 
# Provide a database connection file for the system administrator user.
connection = "<user>/connections/<Connection_file>.sde"

# Process: Create a new database login and user that can create data.
arcpy.CreateDatabaseUser_management(connection, "DATABASE_USER", "dbuser", "t3mpor@rypL@ceholder1")

Save the file with a .py extension.
Run the script from a computer where ArcGIS AllSource (Standard or Advanced) or ArcGIS Server is installed.
For information about running Python from an ArcGIS Server machine, see ArcGIS Server and ArcPy.

Your database now has a user who can create tables and feature classes.

Once the tables and feature classes exist, the data owner can grant privileges on the datasets to other users. See Grant and revoke dataset privileges in databases and enterprise geodatabases for instructions.

Create logins and users with SQL Server tools

To create a user who has different privileges than those granted by the Create Database User tool or has no privileges granted directly to the user, you can use SQL Server tools to do that. Here are some things to keep in mind when you are creating your own logins and users to be used with ArcGIS:

All database users who will create data must have a schema in the database. That schema must have the same name as the username.
You can grant a Windows group access to SQL Server rather than individual Windows logins, which simplifies creation and management of logins. All members of the Windows group can sign in to SQL Server. Server, database, and dataset privileges granted to the group automatically apply to every member of the group. However, be aware that you cannot create one schema to store the data created by all the group members. Each user in the group who creates data in the geodatabase must have its own schema in which to store the data. SQL Server creates a user and schema in the database the first time a group member creates data. This happens automatically; do not create a schema and user manually.

For instructions on using SQL Server tools to create logins, users, and schemas, consult the Microsoft SQL Server documentation.

Feedback on this topic?