• Home
  •  > 
  • Internet
  •  > 
  • Asynchronous programming. Defining an Asynchronous Operation

Asynchronous programming. Defining an Asynchronous Operation

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 " General» 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" Databases" and select " Create a database» .

In the window that opens " Creating a Database" on the " tab General» fill in:

  • Set the database name. The database name must not begin 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 on the SQL server. 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. I wrote in detail about database recovery models and what this parameter affects. When in doubt, choose a simple recovery model.

Having decided on the parameters, click “ OK» .

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

Did this article help you?

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, I will use the version of the SQL server 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 is when a login can identify a user as a Windows account or as a member of a 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. First we will do this using the environment SQL Server Management Studio and then using the Transact-SQL language.

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 on Transact-SQL language 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 the Management Studio GUI).

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 - indicates 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 language T-SQL.

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 database role that this user will have. 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, a login cannot be deleted if the user is currently logged in 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!

12/07/2016 Tim Ford

What a Database Administrator Should Know About Security: An Explanation of Terms and a General Overview of Objects in Practice

SQL Server Security and Trusted Authentication

There are two types of security schemes in Microsoft SQL Server: SQL Server security and trusted authentication (also known as Windows authentication). SQL Server security is a standard login username and password combination, and trusted authentication assumes that the device that is attempting to connect to the instance of SQL Server is approved by the domain authentication procedure, and the results of this verification are passed to the instance of SQL Server: the domain is assumed to be which hosts the SQL Server instance trusts the user account - check performed previously.

Users and applications that attempt to connect to SQL Server through the SQL Server security mechanism must provide a username and password created on the target SQL Server. When connecting using trusted authentication, you only need to set a flag in the connection string to indicate that trusted authentication is used. In this case, the end user or application service account credentials are sent as a security token. If there is a matching username on SQL Server, then access will be granted.

Names and users

There are two levels of access to a SQL Server instance: server (or instance) user accounts and database users. Using accounts, servers allow an external user (in this article, the term "user" refers to any application, service, API, etc. attempting to connect to SQL Server) to make the initial connection to an instance of SQL Server. With SQL-based security, this requires a username and password. In the case of trusted authentication, this is a domain account.

There are two ways to create these user accounts: using Transact-SQL (https://msdn.microsoft.com/en-us/library/ms189751.aspx? f=255&MSPPError=-2147217396) or through the GUI. The procedure for using T-SQL to create accounts is well documented, and it is best to refer to the official Microsoft SQL Server documentation. For now, let's look at how to create an account in the graphical interface. To launch the dialog box to create user accounts, connect to the instance of SQL Server in SQL Server Management Studio (SSMS) in Object Explorer, and then expand the Security\Logins node. Right-click Logins and select New Login from the context menu (Figure 1).

You'll see the account settings dialog shown in Figure 2. You can change your username in the same window.

This is the General tab for creating (and changing) account settings. It differs from the two previously described security schemes. On the General tab you can set:

  • Login name. Used for authentication. In the case of Windows, or trusted authentication, you must specify the name in the format DOMAIN\LOGIN, where LOGIN is the name of the user within the domain from which the user is authenticating. If the SQL Server instance is located in a different domain, then a trust relationship is required between that domain and the SQL Server domain.
  • Password. With SQL Server authentication, the password text box is enabled and you enter both the username and the associated password.
  • Password Policy and Expiration. The password and expiration policy check boxes are also selected in SQL Server authentication mode, and the policies that are in effect in Active Directory in the domain that hosts SQL Server are applied. By assigning a SQL Server username, you can allow users to change their passwords after registration. As a result, the database administrator is denied access to the end user's account name.
  • Certificates, Keys, Credentials. In this beginner's article, we won't cover certificates, keys, and credentials.
  • Default Database Once the connection to SQL Server is established, two steps are performed: authentication (a username must exist for the user's domain credentials if Windows or Trusted authentication is used, or the username/password combination must be passed to the SQL Server instance). This is the first barrier. The second is that the verified username has an associated user object in the default database - the database initially configured as the username context after identity verification. Even if the first hurdle is passed, if there is no corresponding database user in the default database, the connection will not be established and the corresponding entry will be written to the SQL error log. But there are exceptions: if a user's server role is so important that it is necessary to set default implicit rights for him in each database, then it is not necessary to have a corresponding user in the default database. However, I'm getting ahead of myself because we haven't covered database users or server roles yet. Suffice it to note that when you select a default database in the GUI, no associated database user is created. You simply specify what the default database should be. When you do this, you use the User Mapping tab of the Create Login dialog box to create an associated database user.

Let's move on to the next tab, Server Roles, shown in Figure 3. On this page, you can select any SQL Server (instance) level roles for the new user. Server roles are collections of rights, also known as securables, that are packaged into a collection so that you don't have to assign rights to each securable individually. By default, each account is a member of a public role, which allows you to establish a primary connection to an instance of SQL Server. The rest of the article will look at each server role included in Microsoft SQL Server.

The next page of the Create Login dialog box in SQL Server Management Studio is for user account mapping. Each account can have a user in one or more databases. On this page you can create database users associated with the new account. To do this you need to provide the following information.

  • Database. Select the check box next to the database where you want to create an associated user for the account.
  • User Name The user object name does not necessarily match the account name, and we'll show you how this can be changed later.
  • Default Schema Each database user must be assigned to a default schema. A schema is a collection of database objects that are separated logically (but not necessarily physically) from other objects in the database. You can grant a user or group of users rights to all objects in a given schema, for example, grant all users from Accounting (or an accounting application service account) certain rights to all objects in the Billing schema, but not grant access to those objects to other users. When you assign a default schema to a database user, there is no need to include the schema name in T-SQL calls to the database when addressing objects in that schema. This also means that if a user is granted rights to create objects, they will be created in this schema by default unless you specify a schema name when creating the objects. Later in the article we will touch on the concept of circuits.
  • Database Role Membership Just like at the instance or server level, each database has a predefined collection of rights packaged into roles. We'll look at the database roles that come with Microsoft SQL Server a little later.

Let's take a look at the example dialog box for the SQLCRUISE\skipper user account (see Figure 4).

In this example, the user SQLCRUISE\skipper is granted rights to the default database (lifeboat), where the associated username is simply skipper. The default scheme is skipper_only. The other two databases in which users will be created for this account use the same user name as in the username (usually for ease of identification), and the default schema is dbo, which is the default in Microsoft SQL Server for all user-defined objects. More information about this will be provided in the next section. In the case of the lifeboat database, we only provide membership in the public database role, which allows you to connect to the database without additional permissions.

The next page, Securables, presents securables at the server or instance level. As noted above, securables are permissions granted to objects. Securable objects are typically provided in the following cases:

  • the fixed role is too broad (many other rights for the account);
  • The assigned role or set of roles does not fully cover all the rights required for the account.

In our example, I granted SQLCRUISE\skipper membership in the public server role and allowed it to view any object definitions that exist at the server level (see Figure 5).

Finally, we go to the Status page. On this page you can allow or deny access for the user (by default, Grant is selected - allow). Therefore, you can create an account, grant rights, create associated users, and then revoke access. You can return to this window for an existing user and revoke access to the SQL Server instance. Enabling and disabling an account occurs in the same way (see Screen 6). Finally, we can view the user's account status and see if the account has been locked due to too many failed login attempts with an incorrect password.

Each option works successfully if there is only one table named tblFoo in the SQL_Cruise database and the current database context was the SQL_Cruise database. However, only the first option will work correctly, regardless of which database you are currently connected to on the SQL Server instance containing the SQL_Cruise database. The second option will be executed if you are connected to the SQL_Cruise database, regardless of the number of schemas that have tblFoo, since you specified the dbo schema. The third option will generate an error message (see Figure 8) if the SQL_Cruise database has multiple schemas with tblFoo, as shown in Listing 4, where I created both the dbo.tblFoo table and the user.tblFoo table.

Yes, that's right - the object exists, but you get the error message Invalid object name. Never be sure in advance that an object with that name does not exist. The message may indicate a syntax problem.

Fixed roles are included with Microsoft SQL Server at both the server and database levels. However, you can create your own roles if situations arise in which you need to assign the same permissions to many users. Creating custom roles allows you to define these rights only once: when you create the role, not on a per-user or per-user login account basis (depending on database or server roles). In addition to saving time, it eliminates inconsistency when assigning rights to multiple users or accounts.

Use the hyperlinks to navigate through the complete list of Microsoft-provided server roles and database roles. Future articles, as we begin to move from the basics to deeper topics, will cover how to create roles, add users or accounts to those roles, and associate rights with roles at the server and database levels.

Microsoft SQL Server security is a very important topic. It is distinguished by its depth, as well as the originality of its terminology. I hope I have achieved my goal of explaining the various terms and giving a general overview of the objects used in practice. We'll cover a few more topics in these entry-level articles, but I'll soon address more complex issues that arise from this post. As always, I thank readers for their attention and look forward to your comments. I hope this article will help newbie database administrators master the mysteries of SQL.

Listing 1. Code corresponding to the settings made in the GUI

USE GO CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE= GO USE GO CREATE USER FOR LOGIN ALTER USER WITH DEFAULT_SCHEMA= GO CREATE SCHEMA AUTHORIZATION GO USE GO CREATE USER FOR LOGIN ALTER USER WITH DEFAULT_SCHEMA= GO USE GO CREATE USER FOR LOGIN ALTER USER WITH DEFAULT_SCH EMA=GO use GO GRANT VIEW ANY DEFINITION TO GO

Listing 2. Information about system and database users

SELECT name , sid , principal_id , type_desc , default_database_name FROM sys.server_principals WHERE name = "professor"; SELECT name , sid , principal_id , type_desc , default_schema_name FROM lifeboat.sys.database_principals WHERE name = "professor";

Listing 3. Example query for selecting table columns and rows

OPTION 1: FQDN -========================================================= ============================= SELECT * FROM SQL_Cruise.dbo.tblFoo; -==================================================== ======================== - OPTION 2: name defined via schema -================ ===================================================== ======= SELECT * FROM dbo.tblFoo; -==================================================== ======================== - OPTION 3: object name only -==== ===================================================== ===== SELECT * FROM tblFoo; Listing 4. Creating tables with multiple schemas USE GO CREATE SCHEMA AUTHORIZATION GO CREATE TABLE dbo.tblFoo (id INT); CREATE TABLE .tblFoo (id INT); SELECT * FROM tblFoo;


SQL Server Security Basics