Create a SQL Login

To create a login via SQL Server Management Studio (SSMS), follow these steps: 

Step 1: Open SSMS and connect to the SQL Server instance. 
Step 2: Expand the "Security" folder in the Object Explorer. 
Step 3: Right-click on the "Logins" folder and select "New Login...". 
Step 4: In the "Login - New" window, enter the desired login name in the "Login name" field. This will be the username that the user will use to connect to the database. 
Step 5: Choose the authentication type. There are two types: "Windows Authentication" and "SQL Server Authentication".

• Windows Authentication: Select this option if you want the login to use the Windows credentials of the user. This is usually used when connecting from a computer within the same domain as the SQL Server.
• SQL Server Authentication: Select this option if you want to create a username and password specifically for this login. This is useful when connecting from a remote computer or when using a different set of credentials.

Step 6: If you selected "SQL Server Authentication" in the previous step, enter the desired password in the "Password" field and confirm it in the "Confirm password" field.
Step 7: In the "Default database" field, select the database that the login will be associated with. This is the database that the login will connect to by default.
Step 8: Specify the server roles of "public".
Step 9: Click "OK" to create the login.

The newly created login can now be used to connect to the SQL Server database using the specified credentials. It is important to properly manage and assign appropriate access privileges to the login to ensure the security and integrity of the database.

Set the Least Privileges

The principle of least privilege is a security best practice that suggests granting users or logins only the minimum privileges necessary to perform their required tasks. This principle is highly important for SQL Server logins for the following reasons:

1. Security: By granting the least privileges necessary, you reduce the potential attack surface and limit the potential damage that can be caused if a login is compromised. If a login has excessive privileges, an attacker who gains access to that login can potentially perform malicious actions on the database.
2. Data integrity: Limiting the privileges of a login ensures that it can only perform the necessary operations and prevents accidental or unintended modifications to the database. This reduces the risk of data corruption or loss due to human error.
   
3. Compliance: Many regulatory frameworks and industry standards require the implementation of least privilege access controls. By adhering to these standards, organizations can demonstrate their commitment to data security and compliance.
4. Separation of duties: By assigning different levels of privileges to different logins, you can implement a separation of duties model, where multiple individuals or teams are required to collaborate to perform critical operations. This helps prevent unauthorized or inappropriate access to sensitive data.
   
5. Auditing and accountability: With least privilege access, it becomes easier to track and monitor user activities. By assigning specific privileges to each login, you can better identify who performed specific actions and hold them accountable for their actions, if necessary.
   

It is important to regularly review and audit the privileges assigned to SQL Server logins to ensure they are aligned with the principle of least privilege. Granting excessive privileges should be avoided, and any unnecessary or unused privileges should be revoked to maintain a secure and well-managed database environment.

Run the following script to grant the least privileges to the recently created login for AI-DBA.

--Assumed the AiDBA is the login. Replace AiDBA to the recently created SQL login name.
GRANT VIEW SERVER STATE TO AiDBA;
GRANT VIEW ANY DEFINITION TO AiDBA;
GRANT VIEW ANY DATABASE TO AiDBA;
--For each database;
DECLARE @STMT NVARCHAR(MAX);
SET @stmt = N'
USE [?];
BEGIN TRY
       CREATE USER [AiDBA] FOR LOGIN [AiDBA];
END TRY
BEGIN CATCH
       PRINT ''CREATE USER - FAILED'';
END CATCH;
BEGIN TRY
       ALTER ROLE [db_ddladmin] ADD MEMBER [AiDBA];
END TRY
BEGIN CATCH
      PRINT ''ALTER ROLE - FAILED'';
END CATCH;
BEGIN TRY
     GRANT VIEW DATABASE STATE TO AiDBA;
END TRY
BEGIN CATCH
     PRINT ''GRANT USER - FAILED'';
END CATCH;'
EXEC sp_MSforeachdb @stmt;