Problem
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 for SQL Server security.
Solution
Gaining access to SQL Server requires a SQL Login for authentication and a Database User for authorization. Authentication checks whether you have permission to log into a system, whereas authorization specifies what you can do once connected. SQL Server creates a login at the instance level and a user at the database level.
Logging into a SQL Server occurs by either Windows Authentication or SQL Authentication.
- Using Windows Authentication, SQL Server verifies the account name and password when a user connects using a Windows user account. This indicates that Windows has verified the user’s identity. SQL Server does not handle the identity validation and password request.
- When using SQL Server Authentication, SQL Server generates and saves the username and password. Every time they connect using SQL Server Authentication, users must enter their credentials (login and password).
To read up on a detailed explanation of which Authentication Mode to select, read this article: Choose an Authentication Mode.
Creating logins and giving access to databases requires specific permissions. To create a login, the account creating the login must have the SQL Server server role of sysadmin or securityadmin. These fixed server-level roles managemanaging 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 will work through simple SQL code to create a Login and User to access SQL Server. This could be for an application, a website, or general ad-hoc queries.
Create Test Database
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
Create a Login with SSMS GUI
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, 5 different pages appear on the left (image below):
- General – Configure the primary login properties.
- Server Roles – Grant server-wide security privileges to a user.
- User Mapping – Map a user in the database to the SQL Server login.
- Securables – View or set the permissions for securables.
- Status – Set permissions to connect, enable, or disable the login, and set the status of SQL Server authentication.
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:
- Enforce password policy
- Enforce password expiration
- User must change password at next login. When you create the login and user and choose this option, the password must change with the first login.
Select the Default database which is HRDatabase and click OK to create the login.
Create a User with SSMS GUI
After creating the Login, you need to create a User in the database and map it to the Login created above.
Note: User creation and role assignments are possible on the login screen. But, I want to show how to do it 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:
- General – Main screen to set user properties
- Owned Schemas – Set schemas owned by this user. Administer owner permissions. Read more here.
- Membership – Set database role membership. Manage the permissions in your database. Read up on it here.
- Securables – View or set the permissions for securables.
- Extended Properties – Add custom properties to database objects. Read more about it here.
On the General page, type in the User name (HRDBUser). Then, select the Login name we just created and the Default schema (you can leave as dbo). Click OK to create the user.
On the Membership page, check the box to make the HRDBUser a db_owner of the database and click OK.
Refresh the Users for the database by right-clicking on Users and selecting Refresh. Now the user appears in the HRDatabase database.
Testing New SQL Server Login
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 appears. Type in your newly created login HRDBLogin and the password.
When you click the Connect button, a Change Password window appears. SQL Server forces you to change the password for the login. Remember: Checking the option at the login creation allows changing the password at first login.
Change the password as requested and click OK to log into the HRDBLogin login.
Create a Login and User with T-SQL
Alternatively, you can run the SQL script below to create the same Login and User as in the example above.
Logging in as a sysadmin or securityadmin is a requirement 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 running the above SQL code, log in as the HRDBLogin in SSMS and change the password when asked.
Test New Login and User
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
Removing the User and Login
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
Try to drop the Login. An error will occur as the HRDBLogin is still active. 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;
Drop the Test Database
Clean up by dropping the HRDatabase database.
USE master;
GO
-- Drop Database if it exists
DROP DATABASE IF EXISTS HRDatabase;
GO
Next Steps
In the next article, we will look at how to create an ASP.Net website and use the HRDatabase database, as well as the Login and User that we created in this tutorial.
See the following articles as well:
Jan has years of experience in Information Technology which ranges from Unix Administration through System Administration and development, Web and Database Development and Administration and applying Agile methodologies. Jan has been working with databases since 1996 when he had his first exposure to Sybase databases. He got involved with SQL Server in the early 2000’s and had exposure to SQL Server 2000, 2005, 2008 and all the rest. Jan likes to find easy and secure ways to host data and has been using Sybase, SQL Server, and MySQL. He has a great passion for working with data and believe the best way to become an expert in a field is by teaching the subject.
- MSSQLTips Awards: Rookie of the Year Contender – 2022
Great explanation!!