To access SQL Server you need to create a login and to give access to a database you need to create a user. In this tutorial, we look at how to create a SQL Server login along with an associated database user using SSMS and T-SQL code.
When it comes to gaining access to SQL Server there is a need for a SQL Login which is used for authentication and a Database User which is used for authorization. Authentication checks whether you have permission to log in to a system, whereas authorization specifies what you can do once connected. In SQL Server, a login is created at the instance level, and a user is created at the database level.
Logging in to a SQL Server can be achieved by either Windows Authentication or SQL Authentication.
To read up on a detailed explanation of which Authentication Mode to select, read this article: Choose an Authentication Mode.
Specific permissions are required to create logins and give access to databases. To create a login, the account creating the login must have the SQL Server server role of sysadmin or securityadmin. Sysadmin and securityadmin are fixed server-level roles that help you manage the permissions on a SQL Server. These roles are security principals that group other principals and they are server-wide in their permission scope. The discussion of server-level roles is outside of the scope of this tip but you can read more about server-level roles here.
In this tutorial, we are going to work through some simple SQL code to create a Login and User to be used to access SQL Server. This could be for an application, a website, or general ad-hoc queries.
Let's set up an environment and get going.
USE master; GO DROP DATABASE IF EXISTS HRDatabase; GO CREATE DATABASE HRDatabase; GO USE HRDatabase; GO
Step 1 - Create Login
To create a Login with the SSMS GUI, in the Object Explorer expand as follows Security > Logins:
Right-click on Logins and select New Login to open up a dialogue to create a new login.
When the New Login screen opens you will see 5 different pages on the left (can be seen below):
On the General page (see below), type the Login name (HRDBLogin), select SQL Server authentication, type a Password and confirm it, and keep the following items checked:
Select the Default database which is HRDatabase and click OK to create the login.
Step 2 - Create User
After you have created the Login, you need to create a User in the database and map it to the Login created above.
Note: the user could have been created and the roles assigned in the login screen but I would like to show how it can be done on the database level.
To create the user with the SSMS GUI, expand Databases > HRDatabase > Security > Users and right click and select New User.
When the Database User screen opens you will see 5 options on the left:
On the General page, type in the User name (HRDBUser), select the Login name we just created and the Default schema(you can leave as dbo) and click OK to create the user.
On the Membership page, we want to make the HRDBUser a db_owner of the database, so check the box and click OK.
If you refresh the Users for the database (right-click on Users and select Refresh), you should now see the user has been created in the HRDatabase database.
You can now test by trying to log in as the HRDBLogin and you should get a window that forces you to change the password.
When you log in to the SQL Server for the first time after the above steps to create the HRDBLogin and the HRDBUser, just click on the connect icon in the Object Explorer.
A Connect to Server window will open up where you can now type in your newly created login: HRDBLogin and the password.
When you click the Connect button, you will be presented with a Change Password window, where SQL Server will force you to change the password for the login as you checked the option to change the password at first login, when the login was created.
Change the password as requested, click OK and you will be logged in as the HRDBLogin login.
Alternatively, you can run the SQL script below to create the same Login and User as in the example above.
You need to be logged in as a sysadmin or a securityadmin to be able to run the below SQL code. As mentioned above, you can read more about server-level roles here.
USE master; GO -- Create the SQL Server Login CREATE LOGIN [HRDBLogin] WITH PASSWORD = N'MyEasyPwd' MUST_CHANGE , DEFAULT_DATABASE=[HRDatabase] , CHECK_EXPIRATION=ON , CHECK_POLICY=ON /**************************************************/ USE HRDatabase GO -- Create the Database User CREATE USER [HRDBUser] FOR LOGIN [HRDBLogin] GO -- Make the new User the Owner of the database ALTER ROLE db_owner ADD MEMBER [HRDBUser] GO
After you run the above SQL code, log in as the HRDBLogin in SSMS and change the password when you are asked to do so.
Open up a New Query window (as the new logged in: HRDBLogin) and execute the following SQL code:
CREATE TABLE TestTable ( ID INT CONSTRAINT PK_TestTable PRIMARY KEY IDENTITY, TestDescr VARCHAR (80), CreateDate DATETIME CONSTRAINT DF_TestTable_CreateDate DEFAULT getdate() ) -- Test inserting data into the newly created table INSERT INTO TestTable (TestDescr) VALUES ('Testing the Table Create') -- Check that the data was inserted into the table SELECT * FROM TestTable -- Drop the table when done DROP TABLE TestTable
If you want to clean up and remove the User and the Login (in this order), use the SQL code below.
USE HRDatabase GO -- Drop the Role ALTER ROLE [db_owner] DROP MEMBER [HRDBUser] GO -- Drop the User DROP USER [HRDBUser] GO USE master; GO -- Drop the Login DROP LOGIN [HRDBLogin]; GO
If you try to drop the Login and you get an error that the HRDBLogin is still logged in. Just use the sp_who2 command to get the ID of the login session, kill it and then you can drop the login.
The kill command ends a user process that is based on the session ID as you can read more about here. The server level-roles that can end a process with the kill command are sysadmin and processadmin and you can read up about these roles in the server-level roles page mentioned above.
sp_who2 KILL 53;
Clean up by dropping the HRDatabase database.
USE master; GO -- Drop Database if it exists DROP DATABASE IF EXISTS HRDatabase; GO
In the next article, we will take a look at how to create an ASP.Net Website and use the HRDatabase database and also the Login and User that we created in this tutorial.
See the following articles as well:
sql server tutorials" width="250" height="125" />
Jan Potgieter has more than two decades of expertise in the database industry as a Certified Microsoft SQL Server Administrator and Database Developer.
This author pledges the content of this article is based on professional experience and not AI generated.
Article Last Updated: 2022-11-10
