How to create SQL login in SQL Server? | SQL server create user and grant permissions in SQL server 2022 | How do I grant login permissions in SQL Server?

This Article guides,

How to create SQL login in SQL Server?

1. Open SSMS and connect SQL Server instance in SSMS.
2. Select Security tab in SQL Server and click on Login. Right click on Login. Enter SQL Username and Password. You can uncheck the password policy checkbox if required.

3. Assign required permission from Server Roles or User Mapping tab. There are multiple default Server role exists in SQL Server. Sysadmin is highest privilege permission on SQL Server.

Note: Do not assign Sysadmin to user. Grant minimum access to use based on requirement.

4. To grant access on SQL server user, User Mapping tab in SQL Server Login window. DB owner permission can perform any task on particular database if granted. You can grant DB_datareader or DB_datawriter based on requirement.

5. Click OK to create SQL login in SQL Server.

How to create SQL login in SQL Server?

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

--This is Server Role
ALTER SERVER ROLE [sysadmin] ADD MEMBER [test]
GO

--This is Database Role.
USE [AdventureWorks2014]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [AdventureWorks2014]
GO
ALTER ROLE [db_owner] ADD MEMBER [test]
GO

How to create SQL login in SQL Server? or SQL script to create Mutiple SQL users.

How to Create multiple Login, User and Grant Permissions in SQL Server using T-SQL?

--Add username in script to create SQL users.
--Create table for bulk insert
DROP table #UserNameCSV
Create Table #UserNameCSV (UserID int, UserName Varchar(100))
 
--Insert login names from C:\csvtest.txt
INSERT INTO  #UserNameCSV(UserName)
VALUES ('Test1'),('Test2'),('Test3'),('Test4'),('Test5'),('Test6'),('Test7'),('Test8')
 
-- Run Cursor
Declare @UserName Varchar(100)
 
Declare db_Cursor CURSOR FAST_FORWARD FOR
Select UserName from #UserNameCSV
 
OPEN db_Cursor
FETCH NEXT FROM db_Cursor INTO @UserName
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
DECLARE @Sql2 varchar(max) = 'CREATE LOGIN ' + @UserName + ' WITH PASSWORD=N''nC0E3Wo(m##)'' MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON'
 
DECLARE @Sql3 varchar(max) = 'ALTER SERVER ROLE [sysadmin] ADD MEMBER '+@UserName
 
print (@Sql2)
 
print (@Sql3)
 
FETCH NEXT FROM db_Cursor into @UserName
 
END
 
CLOSE db_Cursor
DEALLOCATE db_Cursor

Above SQL Script will create user creation script for each user mentioned in Script like below.

Copy script open new query window in SQL Server SSMS and execute it.

Post execution all users will be created with SYSADMIN Permission. You can add permission in script as per your requirement.