Security Architecture and Access Control

SQL Server implements a three-tier security model comprising Logins, Database Users, and Permissions. A single Login account maps to multiple Database Users across different databases (one-to-many), while each Database User corresponds to a specific database (one-to-one).

Login Accounts

Logins authenticate access to the SQL Server instance. An instance may host multiple databases, and the sa account serves as the system administrator with full privileges created during installation.

USE master;
GO
CREATE LOGIN [DevUser] WITH PASSWORD = 'SecurePass123';
GO

Database Users and Permissions

Once authenticated, a Login maps to a User within a specific database to access objects. Permissions are granted or revoked using standard syntax.

-- Granting permissions
GRANT SELECT ON [Enrollees] TO [DevUser];

-- Revoking permissions
REVOKE SELECT ON [Enrollees] FROM [DevUser];

Variable Management in T-SQL

Variables store data temporarily during execution. They are categorized into Local and Global types.

Local Variables

Scoped to the batch or procedure, prefixed with @. They must be declared before use.

DECLARE @candidateName NVARCHAR(50), @candidateId INT;

-- Assignment methods
SET @candidateName = 'John Doe';
SELECT @candidateId = EnrolleeId FROM Enrollees WHERE EnrolleeName = @candidateName;

-- Retrieve neighboring records
SELECT EnrolleeName, EnrolleeId, Gender 
FROM Enrollees 
WHERE EnrolleeId IN (@candidateId - 1, @candidateId, @candidateId + 1)
ORDER BY EnrolleeId DESC;

Global Variables

System-defined variables prefixed with @@. They are read-only and provide server state information.

Variable	Description
@@ERROR	Error number of the last statement
@@IDENTITY	Last inserted identity value
@@ROWCOUNT	Number of rows affected by the last statement
@@VERSION	Current SQL Server version

SET vs SELECT for Assignment

Feature	SET	SELECT
Multiple Variables	No	Yes
Multiple Result Rows	Error	Assigns last value
No Result Returned	Assigns NULL	Retains original value

DECLARE @addr NVARCHAR(100), @name NVARCHAR(100);

-- SELECT allows multiple assignments
SELECT @addr = 'Seattle', @name = 'Jane Smith';

-- Handling empty results
SELECT @addr = Address FROM Enrollees WHERE 1 = 0; -- Value remains unchanged

Control Flow Statements

Conditional Logic (IF...ELSE)

DECLARE @averageScore INT;
SELECT @averageScore = AVG(CSharpScore) FROM Assessments;

IF (@averageScore >= 80)
    PRINT 'Pass';
ELSE
    PRINT 'Fail';

Loops (WHILE)

WHILE (1 = 1)
BEGIN
    -- Logic here
    BREAK; -- Exit condition
END

Case Expressions

SELECT 
    EnrolleeId,
    Grade = CASE 
        WHEN CSharpScore >= 90 THEN 'A'
        WHEN CSharpScore BETWEEN 80 AND 89 THEN 'B'
        ELSE 'F'
    END
FROM Assessments;

Subqueries and Filtering

Subqueries are nested queries enclosed in parentheses. They can be used in WHERE, HAVING, or SELECT clauses.

Standard Subquery

Must return a single value when used with comparision operators.

SELECT EnrolleeId, EnrolleeName 
FROM Enrollees 
WHERE EnrolleeId > (SELECT EnrolleeId FROM Enrollees WHERE EnrolleeName = 'Alice');

IN Clause

Handles multiple returned values.

-- Find enrollees with high database scores
SELECT EnrolleeId, EnrolleeName 
FROM Enrollees
WHERE EnrolleeId IN (SELECT EnrolleeId FROM Assessments WHERE SQLServerScore > 80);

-- Find enrollees without scores
SELECT EnrolleeId, EnrolleeName 
FROM Enrollees
WHERE EnrolleeId NOT IN (SELECT EnrolleeId FROM Assessments);

EXISTS Clause

Checks for the existence of rows.

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'TestDB')
    PRINT 'Database exists';

Views

Views are virtual tables based on the result set of a query. They do not store data physically but simplify complex joins and restrict access.

Creating and Using Views

-- Drop if exists
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'vw_ExamSummary')
    DROP VIEW vw_ExamSummary;
GO

-- Create View
CREATE VIEW vw_ExamSummary
AS
SELECT TOP 100 
    E.EnrolleeId,
    E.EnrolleeName,
    C.ClassName,
    A.CSharpScore,
    A.SQLServerScore,
    Total = (A.CSharpScore + A.SQLServerScore)
FROM Enrollees E
JOIN Assessments A ON E.EnrolleeId = A.EnrolleeId
JOIN Classes C ON E.ClassId = C.ClassId
ORDER BY Total DESC;
GO

-- Query View
SELECT * FROM vw_ExamSummary;

Limitations

Views generally cannot contain ORDER BY (unless TOP is used), INTO, or reference temporary tables directly for modification.

Stored Procedures

Stored procedures are precompiled collecsions of T-SQL statements. They improve performance, security, and modularity.

Types

1. System: Prefix sp_, stored in master.
2. Extended: Prefix xp_, external DLLs.
3. User-Defined: Created by developers.

Creating Procedures

CREATE PROC usp_GetExamResults
    @MinScore INT = 60,
    @MaxScore INT = 100,
    @FailCount INT OUTPUT
AS
BEGIN
    SELECT EnrolleeName, CSharpScore 
    FROM Enrollees E
    JOIN Assessments A ON E.EnrolleeId = A.EnrolleeId
    WHERE CSharpScore BETWEEN @MinScore AND @MaxScore;

    SELECT @FailCount = COUNT(*) 
    FROM Assessments 
    WHERE CSharpScore < @MinScore;
END

Execution

DECLARE @fails INT;
EXEC usp_GetExamResults @MinScore = 70, @MaxScore = 90, @FailCount = @fails OUTPUT;
SELECT @fails AS 'Total Failures';

Transaction Management

Transactions ensure data integrity through ACID properties (Atomicity, Consistency, Isolation, Durability).

T-SQL Transactions

BEGIN TRANSACTION;
DECLARE @ErrorCount INT = 0;

UPDATE Wallets SET Balance = Balance - 100 WHERE UserId = 1;
SET @ErrorCount = @ErrorCount + @@ERROR;

UPDATE Wallets SET Balance = Balance + 100 WHERE UserId = 2;
SET @ErrorCount = @ErrorCount + @@ERROR;

IF (@ErrorCount = 0)
    COMMIT TRANSACTION;
ELSE
    ROLLBACK TRANSACTION;

ADO.NET Transactions

public static int ExecuteTransaction(List<string> queries)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        conn.Open();
        using (SqlTransaction tran = conn.BeginTransaction())
        {
            try
            {
                int total = 0;
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.Transaction = tran;
                    foreach (string sql in queries)
                    {
                        cmd.CommandText = sql;
                        total += cmd.ExecuteNonQuery();
                    }
                }
                tran.Commit();
                return total;
            }
            catch
            {
                tran.Rollback();
                throw;
            }
        }
    }
}

Indexes

Indexes optimize data retrieval but impact write performance.

1. Clustered: Determines physical storage order (one per table).
2. Non-Clustered: Logical order with pointers to data rows (multiple allowed).

ADO.NET Integration Patterns

Command Execution

Using parameterized queries prevents SQL injection and improves plan caching.

public static int InsertEnrollee(Enrollee model)
{
    string sql = "INSERT INTO Enrollees (Name, Age) VALUES (@Name, @Age)";
    SqlParameter[] params = {
        new SqlParameter("@Name", model.Name),
        new SqlParameter("@Age", model.Age)
    };
    return ExecuteCommand(sql, params);
}

Calling Stored Procedures

public static SqlDataReader CallProcedure(string procName, SqlParameter[] parameters)
{
    SqlConnection conn = new SqlConnection(ConnectionString);
    SqlCommand cmd = new SqlCommand(procName, conn);
    cmd.CommandType = CommandType.StoredProcedure;
    
    if (parameters != null)
        cmd.Parameters.AddRange(parameters);

    conn.Open();
    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}

Helper Class Structure

A robust data access layer should encapsulate connection handling, transaction support, and parameterized execution to ensure security and maintainability.

Database Maintenance

Truncating Logs

USE [master];
GO
ALTER DATABASE [TargetDB] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
DBCC SHRINKFILE (N'TargetDB_log', 0, TRUNCATEONLY);
GO
ALTER DATABASE [TargetDB] SET RECOVERY FULL WITH NO_WAIT;
GO

Execution Strategy Comparison

1. Ad-hoc SQL: Prone to injection, harder to maintain.
2. Parameterized SQL: Secure, efficient.
3. Stored Procedures: Highest security, best performance, modular logic.