At Microsoft SQL Server DBA adds Accounts to an instance of SQL Server, these accounts are mapped to users in separate databases in the instance of SQL Server. Database users who create tables and object classes must have the appropriate permissions to create objects in the database and the schema in which those objects will be created. When used in ArcGIS, the schema name must match the database user name.

You can use a geoprocessing tool or script for this Create Database User to perform the following operations:

• Create or add an account to the SQL Server instance.
• Create a user corresponding to the specified account.
• Create a mapping schema for a user in the specified database.
• Grant the user the access rights necessary to create tables, object classes, or views in the specified database.

Add a user who can create data.

You can run the tool from ArcGIS for Desktop or call the tool in a Python script to create a database user that can create tables, feature classes, and views.

You must connect to the database using an account with rights system administrator in your SQL Server instance to run the tool Create Database User tool.

If you want to create a geodatabase user with a Windows account, the appropriate account must already exist before you run the tool.

Using the Tool Create Database User

1. Launch ArcMap or ArcCatalog.
2. Connect to the database or geodatabase using an account that has system administrator rights on the instance of SQL Server.
3. Open the tool Create Database User.

   The tool is located in the Geodatabase Administration toolset of the Data Management suite.

4. Specify your database connection in the field Input Database Connection.
5. Select whether you will create an account with SQL Server authentication or use an existing account with Windows authentication.
   • Leave unchecked to create a user with SQL Server authentication. Note that by default, SQL Server instances use Windows authentication only. If your instance is not configured to use SQL Server or Windows authentication, you will not be able to create a database-authenticated user.
   • Check Create a user with authentication means operating system(Create Operating System Authenticated User) to use an existing account with Windows authentication.
6. Enter the name of the database user that will be created by the tool.

   If you choose to create an account with SQL Server authentication, the name you enter will also be used for the account.

7. Enter the database user password.
8. If you already have a role defined that you want to add the user to, specify it.
9. Click OK to run the tool.

Run the Python script

To create a user using a script, follow these steps:

1. Create text file on the ArcGIS client machine and copy the following script to a file.

   """ Name: create_database_user.py Description: Provide connection information to a database user. Type create_database_user.py -h or create_database_user.py --help for usage """ # Import system modules import arcpy import os import optparse import sys # Define usage and version parser = optparse . OptionParser (usage = "usage: %prog " , version = "%prog 1.0 for 10.1 release" ) #Define help and options parser . add_option ("--DBMS" , dest = "Database_type" , type = "choice" , choices = [ "SQLSERVER" , "ORACLE" , "POSTGRESQL" , "" ], default = "" , help = "Type of enterprise DBMS: SQLSERVER, ORACLE, or POSTGRESQL.")parser. add_option ("-i" , dest = "Instance" , type = "string" , default = "" , help = "DBMS instance name" ) parser . add_option("-D" , dest = "Database" , type = "string" , default = "none" , help = "Database name: Not required for Oracle")parser. add_option ("--auth" , dest = "Account_authentication" , type = "choice" , choices = [ "DATABASE_AUTH" , "OPERATING_SYSTEM_AUTH" ], default = "DATABASE_AUTH" , help = "Authentication type options (case-sensitive): DATABASE_AUTH, OPERATING_SYSTEM_AUTH. Default=DATABASE_AUTH")parser. add_option ("-U" , dest = "Dbms_admin" , type = "string" , default = "" , help = "DBMS administrator user" ) parser . add_option ("-P" , dest = "Dbms_admin_pwd" , type = "string" , default = "" , help = "DBMS administrator password" ) parser . add_option ("--utype" , dest = "user_type" , type = "choice" , choices = [ "DATABASE_USER" , "OPERATING_SYSTEM_USER" ], default = "DATABASE_USER" , help = "Authentication type options (case-sensitive): DATABASE_USER, OPERATING_SYSTEM_USER. Default=DATABASE_USER")parser. add_option ("-u" , dest = "dbuser" , type = "string" , default = "" , help = "database user name" ) parser . add_option ("-p" , dest = "dbuser_pwd" , type = "string" , default = "" , help = "database user password" ) parser . add_option ("-r" , dest = "role" , type = "string" , default = "" , help = "role to be granted to the user")parser. add_option ("-t" , dest = "Tablespace" , type = "string" , default = "" , help = "Tablespace name" ) # Check if value entered for option try: (options, args) = parser. parse_args() #Check if no system arguments (options) entered if len (sys . argv ) == 1 : print " %s : error: %s \n " % (sys . argv [ 0 ], "No command options given" ) parser . print_help () sys . exit(3) #Usage parameters for spatial database connection database_type = options . Database_type. upper() instance = options. Instance database = options . Database. lower () account_authentication = options . Account_authentication. upper() dbms_admin = options. Dbms_admin dbms_admin_pwd = options . Dbms_admin_pwd dbuser = options . dbuser dbuser_pwd = options . dbuser_pwd tablespace = options . Tablespace user_type = options . user_type role = options . role if (database_type == "SQLSERVER" ): database_type = "SQL_SERVER" if ( database_type == "" ): print (" \n %s : error: \n %s \n " % (sys . argv [ 0 ] , "DBMS type (--DBMS) must be specified.")) parser. print_help () sys . exit (3 ) if (database_type == "SQL_SERVER" ): if ( account_authentication == "DATABASE_AUTH" and dbms_admin == "" ): print (" \n %s : error: %s \n " % (sys . argv [0], "DBMS administrator must be specified with database authentication"))sys. exit (3 ) if ( account_authentication == "OPERATING_SYSTEM_AUTH" and dbms_admin != "" ): print (" \n Warning: %s \n " % ( "Ignoring DBMS administrator specified when using operating system authentication...")) else : if ( dbuser . lower () == "" ): print (" \n %s : error: %s \n " % (sys . argv [ 0 ], "Database user must be specified."))sys. exit (3 ) if ( dbms_admin == "" ): print (" \n %s : error: %s \n " % (sys . argv [ 0 ], "DBMS administrator must be specified!"))sys. exit (3 ) if ( user_type == "DATABASE_USER" and (dbuser == "" or dbuser_pwd == "" )): print (" \n %s : error: \n %s \n " % (sys . argv [0], "To create database authenticated user, user name and password must be specified!")) parser. print_help () sys . exit(3) # Get the current product license product_license = arcpy . ProductInfo() # Checks required license level if product_license . upper() == "ARCVIEW" or product_license . upper () == "ENGINE" : print (" \n " + product_license + " license found!" + "Creating a user in an enterprise geodatabase or database requires an ArcGIS for Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server license.")sys. exit( "Re-authorize ArcGIS before creating a database user.") else : print (" \n " + product_license + "License available! Continuing to create...") arcpy . AddMessage ("++++++++++") # Local variables instance_temp = instance . replace ( " \\ " , "_" ) instance_temp = instance_temp . replace ("/" , "_" ) instance_temp = instance_temp . replace (":" , "_" ) Conn_File_NameT = instance_temp + "_" + database + "_" + dbms_admin if os . environ. get ("TEMP" ) == None : temp = "c:\\temp" else : temp = os . environ. get ("TEMP" ) if os . environ. get ("TMP" ) == None : temp = "/usr/tmp" else : temp = os . environ. get ( "TMP" ) Connection_File_Name = Conn_File_NameT + ".sde" = temp + os . sep + Conn_File_NameT + ".sde" # Check for the .sde file and delete it if present arcpy. env. overwriteOutput = True if os . path. exists ( Connection_File_Name_full_path): os . remove( Connection_File_Name_full_path) try : print (" \n Creating Database Connection File...\n" ) # Process: Create Database Connection File... # Usage: out_file_location, out_file_name, DBMS_TYPE, instnace, database, account_authentication, username, password, save_username_password(must be true) #arcpy.CreateDatabaseConnection_management(temp , Connection_File_Name, database_type, instance, database, account_authentication, dbms_admin, dbms_admin_pwd, "TRUE") arcpy. CreateDatabaseConnection_management(out_folder_path = temp , out_name = Connection_File_Name , database_platform = database_type , instance = instance , database = database , account_authentication = account_authentication , username = dbms_admin , password = dbms_admin_pwd , save_user_pass = "TRUE" ) for i in range (arcpy . GetMessageCount ()) : if "000565" in arcpy . GetMessage(i): #Check if database connection was successful arcpy. AddReturnMessage (i) arcpy. AddMessage (" \n +++++++++" ) arcpy . AddMessage ("Exiting!!" ) arcpy . AddMessage ("++++++++++ \n " ) sys . exit (3) else: arcpy. AddReturnMessage (i) arcpy. AddMessage ("++++++++++ \n " ) print ( "Creating database user... \n " ) arcpy . CreateDatabaseUser_management(input_workspace= Connection_File_Name_full_path, user_authentication_type = user_type , user_name= dbuser, user_password= dbuser_pwd, role= role, tablespace_name= tablespace) for i in range(arcpy. GetMessageCount()): arcpy. AddReturnMessage(i) arcpy. AddMessage("+++++++++ \n" ) except: for i in range(arcpy. GetMessageCount()): arcpy. AddReturnMessage(i) #Check if no value entered for option except SystemExit as e: if e. code == 2 : parser. usage = "" print(" \n" ) parser. print_help() parser. exit(2 )

   You can run the script on a computer with ArcGIS for Desktop(Standard or Advanced), ArcGIS for Server(Standard or Advanced) or ArcGIS Engine with additional module Geodatabase Update.

2. Save the file with the extension .py.
3. Run the script, specifying the options and information appropriate for your instance of SQL Server and the user you want to create.

   In the following example, the name of the created script is create_database_user.py. An account with SQL Server authentication (gisd_owner) has been created on the SQL Server ssi5 instance, and the corresponding schema and user have been created in the gisdata database. The user has not been added to the role.

   create_database_user.py --DBMS SQL_SERVER -i ssi5 -D gisdata --auth DATABASE_AUTH -U sa -P !nocopy! --utype DATABASE_USER -u gisd_owner -p T3mpPass

   Clue:

   Enter -h or --help V command line to display syntax help.

Your database now contains a user who can create tables.

There are several ways that a data owner can create tables in a database or geodatabase. For information about creating tables using ArcGIS, see Overview of adding datasets to a geodatabase.

Because the data set exists, its owner can grant access rights to the data to other users. For instructions, see Granting and Revoking Access Permissions to Datasets.

Creating Accounts and Users Using SQL Server Tools

If you want to create a user that has rights different from those provided by the tool Create Database User, or does not have rights granted directly to it, you can use SQL Server tools for this. There are a few things to keep in mind when creating your own accounts and users to use with ArcGIS:

• All database users who will create data must have a database schema. This schema must have the same name as the user.
• You can provide the group Windows access to SQL Server instead of using separate Windows accounts, making it easier to create and manage accounts. All members Windows groups can connect to SQL Server. Access rights to a server, database, or data set granted to a group are automatically applied to each member of the group. However, you cannot create one schema to store data created by all group members. Each user in a group who creates data in a geodatabase must have their own schema where the data is stored. SQL Server creates a user and schema in the database the first time a group member attempts to create data. This happens automatically; There is no need to manually create a schema and user.

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

After installation, you need to add a user to work with the database, and, accordingly, create a new database. Below we will tell you how to do this.

1. Adding a new user

Launch the program " Wednesday SQL Server Management Studio» (« Start» — « Microsoft SQL Server 2008 R2» — « SQL Server Management Studio») .

In the window that opens, select:

• Server type: " Database Engine Component» .
• Server name in the format " <Имя компьютера>\<Идентификатор экземпляра> " , Where
  <Имя компьютера>— the name of the physical computer on which SQL Server is installed (in my example “ S4»).
  <Идентификатор экземпляра>— set only if connecting to a named instance of SQL Server.
• Authentication:" SQL Server Authentication" or " Windows Authentication»
• Login: SQL Server username.
• Password: In the case of SQL Server authentication, the password for the selected user.

Then click “ Connect» .

If everything is entered correctly, in the window “ Object Browser"We will see a tab with the name of our SQL server. In it, open the tab “ Safety» — « Logins" and in the context menu select " Create Login» .

The window “ Creating a Login". On the " Are common» fill in:

• Login: SQL user name.
• Select authentication: SQL Server.
• We come up with a password for the user.

(If necessary, you can define other security settings). Then go to the “ Server roles» .

On this page you must specify for this user. For example, if you need to create a user with administrative rights, you need to set the role for him

• sysadmin

If a user is created to connect programs or, then specifying the roles is sufficient

• dbcreator
• processadmin

• public

assigned to all users.

Having specified all the necessary roles for the user being created, click “ OK» .

This completes the user creation procedure.

2. Create a new database

To add a new database, in " Microsoft SQL Server Management Studio environment"Right-click on the "tab" Database" and select " Create a database» .

In the window that opens " Database creation" on the " tab Are common» fill in:

• Set the database name. The database name must not start with a number or have spaces in the name, otherwise we will receive an error:
  « Incorrect syntax near the %database name%" construct.
• Select the user created in the previous step as the owner.

Then go to the “ Options» .

Here you need to select “ Recovery Model» databases and « Compatibility level". These parameters depend on the application that will be used with the database being created. SQL server e. For example, you need to set

• Compatibility level: " SQL Server 2000 (80)» .

You should pay very close attention to the parameter “ Recovery Model» the database being created. Details about database recovery models and what this parameter influences, I wrote. If in doubt, choose simple model recovery.

Having decided on the parameters, click “ OK» .

After which we should see the newly created database in the list.

Did this article help you?

In this short article we will look at SQL query, which allows add new users to the database, as well as change the parameters of existing ones.

Let me give you an example right away SQL query to create a new database user:

GRANT SELECT, INSERT, UPDATE, CREATE, PROCESS ON *. * TO "MyUser"@"localhost" IDENTIFIED BY "123456" WITH GRANT OPTION MAX_QUERIES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 10

Now let's take a look SQL query to change existing users:

GRANT SELECT, INSERT, UPDATE, CREATE, PROCESS ON *. * TO "MyUser"@"test1.ru" IDENTIFIED BY PASSWORD "565491d704013245" WITH GRANT OPTION MAX_QUERIES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 10

As you can see, the syntax is EXACTLY the same as for creating a new user. The only difference is that the user " MyUser" already exists. That's all.

And finally removing a user from the database. There is one interesting point here. The fact is that the data of database users is itself stored in database tables. Therefore, to delete a user, you must delete records in the corresponding tables. Here's the code:

DELETE FROM `user` WHERE CONVERT(User USING cp1251) = CONVERT("MyUser" USING cp1251) AND CONVERT(Host USING cp1251) = CONVERT("test1.ru" USING cp1251)
DELETE FROM `db` WHERE CONVERT(User USING cp1251) = CONVERT("MyUser" USING cp1251) AND CONVERT(Host USING cp1251) = CONVERT("test1.ru" USING cp1251)
DELETE FROM `tables_priv` WHERE CONVERT(User USING cp1251) = CONVERT("MyUser" USING cp1251) AND CONVERT(Host USING cp1251) = CONVERT("test1.ru" USING cp1251)
DELETE FROM `columns_priv` WHERE CONVERT(User USING cp1251) = CONVERT("MyUser" USING cp1251) AND CONVERT(Host USING cp1251) = CONVERT("test1.ru" USING cp1251)

A lot, I don’t argue, but it’s the only way delete user. Or manually via PHPMyAdmin. The only thing you need to change in this template is the username and host, and you probably won't have to change anything else.

Hi all! Now we will look at examples creating and deleting users in the Microsoft SQL Server DBMS both using Transact-SQL statements and using the Management Studio environment.

The process of creating users in MS SQL Server includes two stages:

1. Creating a SQL Server login. This name is required to allow the user to connect to the SQL Server instance;
2. Creating a database user. In this case, we already grant the user permissions on database objects.

Note! As an example, my SQL server will be Microsoft SQL Server 2012 Express. A test database Test has been created on this SQL server.

Creating a Login on MS SQL Server

Before you start creating a login for the SQL server, you need to decide on the authentication method. There are two options:

1. Windows Authentication– this is when the login name can identify the user as an account Windows entry or as a member of the Windows group ( including domain accounts and groups);
2. SQL Server Authentication. In this case, the login only exists in SQL Server.

Let's look at a couple of examples of creating a login on a SQL server. We'll do this first using SQL Server Management Studio and then using Transact-SQL.

Creating a Login Using SQL Server Management Studio

We launch Management Studio, then in the object browser we find the item “ Safety", open it with a plus sign, right-click on the item " Logins" and select the item " Create Login».

Next, as an example, let's create a test login with SQL Server authentication. We provide a login name, create a password and confirm it. We can also check several options such as use password policy, default database, default language and others.

Then click on the button “ OK", after which the login TestLogin will be created. By default, this login will be enabled and will have the rights of the "public" server role.

Creating a Login Using Transact-SQL

To create a login in Transact-SQL, you need to open the query editor in Management Studio and run the following instructions (it does exactly the same thing as our actions above in graphical interface Management Studio).

CREATE LOGIN WITH PASSWORD=N"Pa$$w0rd", DEFAULT_DATABASE=, DEFAULT_LANGUAGE=[Russian], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON GO

In other words, to create a login in the SQL server, the instruction is used CREATE LOGIN.

Creating a SQL Server Login with Windows Authentication

To create a Windows Authenticated login, run the following SQL statement:

CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE=, DEFAULT_LANGUAGE=[Russian]; GO

• ComputerName\NameUser is the Computer Name\User Name;
• FROM WINDOWS – specifies that Windows authentication will be used;
• WITH DEFAULT_DATABASE= – default database;
• DEFAULT_LANGUAGE=[Russian] – default language.

Disabling and enabling logins in MS SQL Server

If necessary, you can temporarily disable the login in order to block the user from accessing the server.

Disable ALTER LOGIN TestLogin DISABLE; --Enable ALTER LOGIN TestLogin ENABLE;

Creating a database user in MS SQL Server

Once the login has been created, you can proceed to create a database user, i.e. mapping a user to a login.

Let's create a TestLogin user also in two ways i.e. using Management Studio and T-SQL language.

Creating a Database User Using Management Studio

Open Management Studio, find the desired database in the object browser and open it with the plus sign. Then also use the plus sign to open the item “ Safety" and click on the folder " Users"right-click and select " Create a user».

Next, enter the username and login name that corresponds to this user ( in my case the names are the same), and also indicate the default scheme ( if not specified, the dbo scheme will be assigned).

Let's also immediately note the role the database will have this user. On the page " Membership"I checked the box next to the role db_datareader, i.e. the user will have rights to read data from user tables. Click " OK».

Creating a Database User Using Transact-SQL

The following T-SQL statement creates a database user ( default scheme dbo) and assigns it the db_datareader role, i.e. does the same thing as we did a little earlier in the Management Studio graphical interface.

USE Test GO CREATE USER FOR LOGIN WITH DEFAULT_SCHEMA= GO ALTER ROLE ADD MEMBER ; GO

Thus, the instructions CREATE USER used to create a database user.

Removing Database User and Login in MS SQL Server

In order to delete a database user, you can write a simple SQL statement, for example

DROP USER Testlogin;

Or use the graphical tool Management Studio, i.e. in the object browser, in the desired database, select " Security -> Users" and right-click on the user you want to delete and select " Delete».

Note! Users who own securable objects cannot be removed from the database.

You can also use Management Studio's graphical tool ( those. “Security -> Logins” right-click on the name, and then click on “Delete”) and Transact-SQL statement i.e.

DROP LOGIN TestLogin;

Note! The current login cannot be deleted, nor can the login that owns any server-level securable object or SQL Server Agent job. Also, the login cannot be deleted if the this moment the user is connected to the system. It is possible to delete a login without deleting the associated database user, but this will result in users who are no longer associated with their accounts.

That's all I hope, the material was useful to you, bye!

The set of actions is similar).

1. Adding a new user

Let's launch the utility. IN Microsoft Windows server 2012 R2 it can be found in the list of all programs.

At Microsoft Windows Server 2008 R2 in the "menu" Start" (Start) - " Microsoft SQL Server 2012" - "Wednesday SQL Server Management Studio».

In the object browser, open the tab " Safety"(Security), right-click on the tab " Logins"(Logins) and in the context menu select " Create a login...» (New Login...)

The window for creating a login name (Login - New) will open. Now you need to decide on the authentication option for the new user. There are 2 options:

• Password Authentication - SQL Server Authentication.
• Access for a specific Windows user - Windows authentication.

2. SQL Server Authentication

First, let's look at the first authentication method. For example, let's create a user for . Specify the login name, select “ SQL Server Authentication"(SQL Server Authentication) and enter the user's Password. Next, uncheck/check the following parameters:

• Enforce password policy
• Enforce password expiration
• User must change password at next login

For this task, we leave only the first parameter enabled.

I also immediately recommend choosing the default language. If you are using English version SQL Server, then the service messages that SQL Server will transmit to the application connected under this user (in this case, the program 1C:Enterprise, therefore, to the end user working in the program) will be transmitted to English language. If the default language for the user is selected, for example, Russian, then service messages will be transmitted in Russian.

Set the necessary parameters and go to the “ Server roles"(Server Roles).

Here we select the set of rights of the user to be added. To do this, mark the required server roles. A full description of predefined server roles and their capabilities can be found. For the current task, select:

• dbcreator
• processadmin
• public

Then click “ OK» to save completed actions.

3. Windows Authentication

Now let's add a SQL Server administrator by selecting it from current users Windows. To do this, create a new user and specify the authentication method “ Windows Authentication» (Windows authentication). Next, to enter your login name, click “ Find"(Search...), then " Additionally"(Advanced...), in the next window " Search"(Find Now) and selecting the required user from the list, close all windows by clicking on " OK».

Let's go to the tab " Server roles"(Server Roles) and in accordance with the task we will indicate the roles:

• public
• sysadmin

Click " OK» to save the new user.

Now in the list of logins we can see the newly created users among others.

Did this article help you?