Manage permissions with Windows groups in SQL Server

Question

I don't understand what a practice with "Windows group" refers to. This says the following:

1. Create a Windows group for each user.
2. Add the Windows users to the corresponding group.
3. Create a SQL Server login for each Windows group corresponding to a profile.
4. Grant the necessary permissions to each Windows group.
5. Create a standard role for each user profile
6. Grant the necessary permissions to each role.
7. Create a login for each application user and grant access to the database and its corresponding role.

I think I've already done steps 5-7, but I don't understand what you mean in the previous points.

This is what I have done:

USE master
GO

USE PRACT7

--Roles
CREATE ROLE Rol_Lector 
CREATE ROLE Rol_Editor
CREATE ROLE Rol_Datos
CREATE ROLE Rol_Administrador

--Login
CREATE LOGIN Log_Juan WITH PASSWORD= '1234'
CREATE LOGIN Log_Pedro WITH PASSWORD= 'editor'
CREATE LOGIN Log_Roberto WITH PASSWORD= 'datos'
CREATE LOGIN Log_Juan_Manuel WITH PASSWORD= 'administrador'

DROP LOGIN Log_Juan

--Usuarios
CREATE USER Juan FOR LOGIN Log_Juan
CREATE USER Pedro FOR LOGIN Log_Pedro
CREATE USER Roberto FOR LOGIN Log_Roberto
CREATE USER Juan_Manuel FOR LOGIN Log_Juan_Manuel

--ASIGNACION roles
--CREATE ROLE Sales;  
--ALTER ROLE Sales ADD MEMBER Barry;  
--ALTER ROLE Sales DROP MEMBER Barry; 

ALTER ROLE Rol_Lector ADD MEMBER Juan
ALTER ROLE Rol_Editor ADD MEMBER Pedro
ALTER ROLE Rol_Datos ADD MEMBER Roberto
ALTER ROLE Rol_Administrador ADD MEMBER Juan_Manuel

--Asignar permisos
GRANT SELECT
ON CAJEROS
TO Rol_Lector

GRANT DELETE, ALTER
ON CAJEROS
TO Rol_Editor

GRANT DELETE, ALTER, UPDATE, INSERT
ON CAJEROS
TO Rol_Datos

GRANT ALL PRIVILEGES
ON CAJEROS
TO Rol_Administrador
WITH GRANT OPTION

Answer 1

A Windows group is not configured inside of SQL Server. You can use this link to see how to create a group account in Active Directory. (Step 1 and 2) When the group is created, you can add them as SQL Server Login (step 3). To do this, you need to adjust your syntax to use FROM WINDOWS:

CREATE LOGIN [MYDOMAIN\UserGroup] FROM WINDOWS;

You can then grant these logins your rights in SQL.

Link to microsoft documentation to create group

Answer 2

The steps 1 to 4 are to create a user and add it to a group (can be either local or domain) and set up Windows authentication in SQL Server with proper privileges.

The reason that practices suggest you to use Windows user/group instead of SQL Server authentication is that it has stronger security features than SQL Server, e.g. password complexity enforcement, password ages etc.

This is noted in the CIS security benchmark for MS SQL Server.