How to create and delete MSSQL users

Let us learn how to create users for the MSSQL database and how to delete users after creation.

MSSQL user description

• You can create and delete users in MSSQL with dba privileges.
• When you create a user, please grant permission after creation according to the purpose of the create user.
• When specifying a password, make it difficult to create it so that there is no security problem.
• Please give ownership rights to users required for database instead of dba rights.

MSSQL user create, delete

How to create through SQL Server Management Studio (SSMS)

• Connect to the SSMS Open Database.

1-1. Select Object explorer - Security-Login Click - Right Mouse - new Login.

1-2. Login New Screen.

• Enter Login name.
• Select SQL Server authentication.
• Enter your password.
• Uncheck the Enforce passworld expiration check box.
• In the Default database, select the sample DB that you created previously.

1-3. Login New Screen - Select User Mapping.

• In Users mapped to this login, select sampleDB.
• Select db_owner in the Database role membership area.

1-4. Select the Login New Screen - Status tab.

• Verify that login - Enabled is selected.

1-5. Verify that test user is generated.

Created using Transact-SQL

• Access the SSMS Open database.

2-1. In the SQL Run window, type the source, and click Execute.

2-2. Check the users created in Object explorer.

USE [master]
GO
CREATE LOGIN [tsqluser] WITH PASSWORD=N'PAAAA', DEFAULT_DATABASE=[sampleDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
use [master];
GO
USE [sampleDB]
GO
CREATE USER [tsqluser] FOR LOGIN [tsqluser]
GO
USE [sampleDB]
GO
ALTER ROLE [db_owner] ADD MEMBER [tsqluser]
GO

Deleting a user through SQL Server Management Studio (SSMS)

3-1. Object Explorer Screen

• Select Security - Select the user you created - Right mouse - Delete.

3-2. Check the user you created - click the ok button.

• When prompted, click Yes to completely delete the user.

Delete user created using Transact-SQL

4-1. Select the New Query window.

• Create a delete source.

4-2. Verify that the user created by Security has been deleted.

USE [master]
GO
DROP LOGIN [tsqluser]
GO

How to deal with errors

Message confirmation. Msg 15434, Level 16, State 1, Line 3
This login cannot be deleted because the user is currently logged in as ‘tsqluser’.

USE [master]
SELECT session_id
FROM sys.dm_exec_sessions
WHERE login_name = 'tsqluser'
go
KILL 79   
go
USE [master]
GO 
DROP LOGIN [tsqluser]
GO