In this article we will describe the main ways to create and delete users using the example of version 2012: in the Management Studio environment and via Transact-SQL instructions.
SQL Server users are created in two steps:
1.The first step is to create a SQL Server login name. The user will use it to further connect to the DBMS instance.
2.Then you need to create a database user. This is how the permission to work with specific SQL database objects is granted.
Let's discuss each stage in more detail.
Creating a login name
Before creating a name to connect to SQL Server, you must select an authentication method:
1.Windows Authentication. Allows you to add an administrator by selecting one from among specific Windows accounts.
2.SQL-Server Authentication. Authentication is done with a password, and the login name exists only in SQL Server.
Depending on the selection, one or another method of creating a login name is used, discussed below.
Using the SQL Server Management Studio graphical interface
Start the Management Studio environment, open the Object Explorer and navigate to the path "Security" => "Logon Names". Right-click and select the "Create Login Name" line.
In the window that opens, you can select a login name with SQL Server authentication, set it, make up and confirm the password. You can also configure a number of other settings here, such as setting the password expiry date, selecting the language and the default database.
When everything is ready, click "OK". That's it, the login name has been created and is enabled by default with the right to be the publisher server role (public).
Using the Transact-SQL language in the SQL Server Management Studio console
Launch Management Studio and open the query editor in it, where you execute the CREATE LOGIN instruction - so you can create a name to connect to the SQL server with password verification.
With Windows Authentication
Create a Windows authenticated login name by executing the following SQL statement:
CREATE LOGIN [ComputerName\NameUser]
FROM WINDOWS WITH DEFAULT_DATABASE=[Test],
DEFAULT_LANGUAGE=[русский];
GO
In this code you need to replace:
● ComputerName\NameUser - the name of the computer or user;
● FROM WINDOWS - Windows authentication type;
● WITH DEFAULT_DATABASE=[Test] - selection of the default database;
● DEFAULT_LANGUAGE=[Russian] - selection of the default language.
Disabling and enabling login names in MS SQL Server
Sometimes situations arise where a user is compromised. Their login name needs to be disabled to temporarily block access to the server.
To disable:
ALTER LOGIN TestLogin DISABLE;
To enable:
ALTER LOGIN TestLogin ENABLE.
Creating a SQL Server database user
Now you can proceed to the second stage of database user creation - mapping the user to the login name. This can be done in two ways: using the Management Studio GUI and using T-SQL instructions.
Using SQL Server Management Studio
In Management Studio, a new SQL user is created in the following way. Open the Object Explorer and navigate to "Databases" => "Test" => "Security" => "Users". Right-click and select the "Create User" line.
In the window that opens, enter the desired user name and the login name corresponding to this user. There also specify the default schema (if you do not do this, the dbo schema will be used).
In the "database user" window in the "membership" tab, you can assign the desired role to the created user, for example db_datareader (data reader).
Using Transact-SQL
You can create a SQL Server user with the default dbo schema and the db_datareader role by executing the following T-SQL instruction:
USE Test
GO
CREATE USER [TestLogin]
FOR LOGIN [TestLogin]
WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [TestLogin];
GO
How to delete a user in SQL Server
Sometimes administrators need to delete a SQL user. This can be done, firstly, by using the DROP USER Testlogin instruction.
Secondly, you can use the Management Studio GUI. In this case, in the object browser in the current database go to the path "Security" => "Users" and, by right-clicking, select the line "Delete".
To delete the login name you can use the DROP LOGIN TestLogin instruction.
In Management Studio, the login name is deleted by navigating to the path "Security" => "Login Names" and right-clicking and then selecting the line "Delete".
What kind of user login errors can occur?
Sometimes errors occur when creating an SQL user. For example, when executing Transact-SQL instructions, exceptions occur because the selected name is not a valid login name or permission is missing.
To protect your business from user login errors when configuring Microsoft SQL Server we recommend contacting professionals. At DB Serv you can get a wide range of SQL Server administration and support services from qualified engineers.
