By: Jan Potgieter |Updated: 2022-11-10 |Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | > Security
Problem
To access SQL Server you need to create a login and to give access to adatabase you need to create a user. In this tutorial, we look at how to create aSQL Server login along with an associated database user using SSMS and T-SQLcode.
Solution
When it comes to gaining access to SQL Server there is a need for a SQL Loginwhich is used for authentication and a Database User which is used for authorization.Authentication checkswhether you have permissionto log in to a system, whereas authorizationspecifies what youcan do once connected. In SQL Server, a loginis created at the instancelevel, and a user is createdat the databaselevel.
Logging in to a SQL Server can be achieved by either Windows Authenticationor SQL Authentication.
- Using Windows Authentication, SQL Server verifiesthe account name and password when a user connects using a Windows user account.This indicates that Windows has verified the user's identity. Both the identityvalidation and the password request are not handled by SQL Server.
- When using SQL Server Authentication, the user nameand password are both generated using SQL Server and saved there. Every time theyconnect using SQL Server Authentication, users are required to 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.
Specific permissions are required to create logins and give access todatabases. To create a login, the account creating the login must havethe SQL Server server role of sysadminor securityadmin. Sysadmin and securityadmin are fixed server-level roles that help you managethe permissions on a SQL Server. These roles are security principals that groupother principals and they are server-wide in their permission scope. The discussion of server-level rolesis outside of the scope of this tip butyou can read more aboutserver-level roles here.
In this tutorial, we are going to work through some simple SQL code to createa Login and User to be used to access SQL Server. This could be for anapplication, 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 and User with SSMS GUI
Step 1 - Create Login
To create a Login with the SSMS GUI, in the Object Explorer expand as followsSecurity > Logins:
Right-click on Logins and select New Login to openup 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):
- General - this is where you configure the primary loginproperties
- Server Roles -this is used to grant server-wide security privileges to a user.
- User Mapping - this is used to 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 theLogin name (HRDBLogin), select SQLServer authentication, type a Password and confirmit, and keep the following items checked:
- Enforce password policy
- Enforce password expiration
- User must change password at next login - when youcreate the login and use the User must change password at next loginoption, the password will have to be changed withthe first login.
Select the Default database which is HRDatabaseand click OK to create the login.
Step 2 - Create User
After you have created the Login, you need to create a Userin the database and map it to the Login created above.
Note: the user could have been created and the roles assigned inthe 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:
- General - main screen to set user properties
- Owned Schemas- schemas owned by this user canbe set here. This is whereowner permissions can be set to administer them. Read up on ithere.
- Membership- database role membership canbe set here. Managing thepermissions in your database can be set here. Read up on ithere.
- Securables- view or set the permissions forsecurables.
- Extended Properties- adding customproperties to database objects. Read more about ithere.
On the General page, type in the User name (HRDBUser), select the Login namewe just created andthe Default schema(you can leave as dbo) and click OK to create theuser.
On the Membership page, we want to make theHRDBUser a db_owner of the database, socheck the box and click OK.
If you refresh the Users for the database (right-click on Users and selectRefresh), you should nowsee the user has been created inthe HRDatabase database.
Testing New Login
You can now testby trying to log in as the HRDBLogin and you should get a windowthat forces you to change the password.
When you log into the SQL Server for the first time after the above steps to create the HRDBLoginand the HRDBUser, just click on the connect icon in the ObjectExplorer.
A Connectto Server window will open up where you can now type in your newly createdlogin: HRDBLogin and the password.
When you clickthe Connect button, you will be presented with a ChangePassword window, where SQL Server will force you to change the passwordfor the login as you checked the option to change the password at first login, whenthe login was created.
Change the passwordas requested, click OK and you will be logged inas the HRDBLogin login.
Create a Login and User with T-SQL
Alternatively, you can run the SQL script below tocreate the same Login and User as in the example above.
You need to be logged in as a sysadmin ora securityadmin to be able to run the below SQL code. As mentioned above, you can read more aboutserver-level roles here.
USE master;GO -- Create the SQL Server LoginCREATE LOGIN [HRDBLogin] WITH PASSWORD = N'MyEasyPwd' MUST_CHANGE, DEFAULT_DATABASE=[HRDatabase], CHECK_EXPIRATION=ON, CHECK_POLICY=ON /**************************************************/ USE HRDatabaseGO -- Create the Database UserCREATE USER [HRDBUser] FOR LOGIN [HRDBLogin]GO -- Make the new User the Owner of the databaseALTER ROLE db_owner ADD MEMBER [HRDBUser]GO
After you run the above SQL code, log in as theHRDBLogin in SSMS and change the password when youare asked to do so.
Test New Login and User
Open up a New Query window (as thenew 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 tableINSERT INTO TestTable (TestDescr) VALUES ('Testing the Table Create') -- Check that the data was inserted into the tableSELECT * FROM TestTable -- Drop the table when doneDROP TABLE TestTableRemoving the User and Login
If you want to clean up and remove the User and theLogin (in this order), use the SQL code below.
USE HRDatabaseGO -- Drop the RoleALTER ROLE [db_owner] DROP MEMBER [HRDBUser]GO -- Drop the UserDROP USER [HRDBUser]GO USE master;GO -- Drop the LoginDROP LOGIN [HRDBLogin];GO
If you try to drop the Login and you get an error thatthe HRDBLogin is still logged in. Just use thesp_who2 command toget the ID of the login session, kill it and then you can drop the login.
The kill command ends a user processthat is based on the session ID as you can read more abouthere. The server level-roles that can end a process withthe kill command are sysadmin and processadmin and you can readup about these roles in theserver-level roles page mentioned above.
sp_who2 KILL 53;
Drop the Test Database
Clean up by dropping the HRDatabasedatabase.
USE master;GO -- Drop Database if it existsDROP DATABASE IF EXISTS HRDatabase;GO
Next Steps
In the next article, we will take a look at how to create an ASP.Net Website and usethe HRDatabase database and also the Login and User that we createdin this tutorial.
See the following articles as well:
- SQL Server Security Tips
About the author
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.
View all my tips
