Sql Server Implementing Security And Performance Tuning Complete Guide

SQL Server Implementing Security

1. Creating and Managing Logins

Step-by-Step Example:

• Goal: Create a new login named JohnDoe that uses Windows Authentication.

-- Create a new login using Windows Authentication
CREATE LOGIN [Domain\JohnDoe] FROM WINDOWS;

• Goal: Create a new login named JaneDoe with a SQL Server password.

-- Create a new login using SQL Server Authentication
CREATE LOGIN JaneDoe 
WITH PASSWORD = 'P@ssw0rd!',
MUST_CHANGE, CHECK_EXPIRATION = ON;

• Goal: Alter JaneDoe login to require a new password on next login.

-- Alter an existing login to make it expire and force a password change
ALTER LOGIN JaneDoe
WITH CHECK_EXPIRATION = ON,
CHECK_POLICY = ON,
MUST_CHANGE;

2. Creating and Managing Users

Users are created within individual databases based on logins.

Step-by-Step Example:

• Goal: Create a user named JohnDoe from the Domain\JohnDoe login in the Sales database.

USE Sales;
GO

-- Create a new user based on the Windows login
CREATE USER JohnDoe FOR LOGIN [Domain\JohnDoe];

• Goal: Create a user named JaneDoe from the JaneDoe login in the HR database.

USE HR;
GO

-- Create a new user based on the SQL Server login
CREATE USER JaneDoe FOR LOGIN JaneDoe;

3. Role-Based Access Control (RBAC)

Assign roles to users to manage permissions.

Step-by-Step Example:

• Goal: Add JohnDoe to the db_datareader role in the Sales database, allowing him to read all data in the database.

USE Sales;
GO

-- Add user to db_datareader role
EXEC sp_addrolemember 'db_datareader', 'JohnDoe';

• Goal: Add JaneDoe to the db_datawriter role in the HR database, allowing her to write (modify) data in the database.

USE HR;
GO

-- Add user to db_datawriter role
EXEC sp_addrolemember 'db_datawriter', 'JaneDoe';

4. Assigning Explicit Permissions

Explicitly grant or deny permissions to specific entities.

Step-by-Step Example:

• Goal: Grant JohnDoe permission to execute stored procedures in the Sales database.

USE Sales;
GO

-- Grant EXECUTE permission to the user
GRANT EXECUTE ON SCHEMA::dbo TO JohnDoe;

• Goal: Deny JaneDoe permission to delete any data in the Employees table of the HR database.

USE HR;
GO

-- Deny DELETE permission on Employees table
DENY DELETE ON dbo.Employees TO JaneDoe;

SQL Server Performing Performance Tuning

1. Identifying Performance Bottlenecks

Use system stored procedures and dynamic management views (DMVs).

Step-by-Step Example:

• Goal: Identify top 10 most resource-intensive queries.

-- Select top 10 queries based on CPU time used
SELECT TOP 10 
    qs.total_worker_time AS Total_CPU_Time,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS Avg_CPUTime_Per_Execution,
    st.text AS SQL_Text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

2. Updating Statistics

Updating statistics helps the query optimizer choose better execution plans.

Step-by-Step Example:

• Goal: Update statistics for all user tables in the HR database.

USE HR;
GO

-- Update statistics for all tables in HR database
EXEC sp_updatestats;

3. Index Maintenance

Maintaining indexes ensures efficient data retrieval.

Step-by-Step Example:

• Goal: Rebuild fragmented indexes above 30% for all tables in the Sales database.

USE Sales;
GO

-- Identify and rebuild fragmented indexes
DECLARE @TableName NVARCHAR(255)
DECLARE @IndexName NVARCHAR(255)
DECLARE @SQLStatement NVARCHAR(2000)

SET NOCOUNT ON;

-- Cursor to iterate through all tables in sales database with fragmented indexes > 30%
DECLARE CURSOR_INDEX REBUILD CURSOR FOR
SELECT 
    t.name AS TableName,
    i.name AS IndexName
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ips
INNER JOIN 
    sys.tables t ON ips.object_id = t.object_id
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN 
    sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE 
    avg_fragmentation_in_percent > 30
    AND i.type IN (1, 2) -- Clustered/Non-clustered indexes
    AND s.name IS NOT NULL;

OPEN CURSOR_INDEX REBUILD;
FETCH NEXT FROM CURSOR_INDEX REBUILD INTO @TableName, @IndexName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQLStatement = N'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD;'
    PRINT @SQLStatement
    EXEC sp_executesql @SQLStatement

    FETCH NEXT FROM CURSOR_INDEX REBUILD INTO @TableName, @IndexName;
END

CLOSE CURSOR_INDEX REBUILD;
DEALLOCATE CURSOR_INDEX REBUILD;

4. Configuring Server Memory Settings

Adjusting SQL Server memory allocation can enhance performance.

Step-by-Step Example:

• Goal: Set SQL Server to use up to 80% of the server's total physical memory.

-- Alter server configuration settings
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

EXEC sp_configure 'max server memory', 8192; -- Adjust this value based on your total RAM
RECONFIGURE;
GO

EXEC sp_configure 'min server memory', 1024; -- Recommended to set a minimum
RECONFIGURE;
GO

5. Managing TempDB Configuration

Optimizing TempDB can reduce contention and improve performance.

Step-by-Step Example:

• Goal: Add a new TempDB data file.

-- Adding a new data file to the TempDB
ALTER DATABASE tempdb 
ADD FILE (NAME='tempdev2', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdev2.ndf',
SIZE=1024MB, MAXSIZE=UNLIMITED, FILEGROWTH=512MB);
GO

6. Query Optimization

Writing efficient queries is key to performance tuning.

Step-by-Step Example:

• Goal: Optimize a query by adding an index to the Orders table on the OrderDate column.