Why should you care?

SQL injection and over-privileged app logins still cause breaches. The database is part of the security boundary.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
-- App should call procedures with parameters — not concatenate SQL
CREATE OR ALTER PROCEDURE dbo.usp_FindCustomerByEmail
    @Email NVARCHAR(256)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CustomerId, FullName, Email
    FROM dbo.Customers
    WHERE Email = @Email;
END
GO
-- Least privilege idea (run as admin once):
-- GRANT EXECUTE ON dbo.usp_FindCustomerByEmail TO [dataverse_app];
-- DENY SELECT ON dbo.Customers TO [dataverse_app];

What happened?

• The proc accepts @Email as a true parameter.
• Granting EXECUTE without table SELECT stops the app login from reading arbitrary customer rows.

Practice next

1. Create usp_FindCustomerByEmail.
2. Call it with a parameter — never concatenate unsanitized email into SQL.
3. Plan an app login with EXECUTE-only grants.
4. Attempt SELECT as a limited user and confirm denial.
5. Add row-level security in a later lesson for tenants.

Remember

Parameterize everything. Grant least privilege. Treat dynamic SQL as dangerous.

App login locked down

DataVerse API uses dataverse_app with EXECUTE on procs only.

Outcome: Even with injection bugs elsewhere, raw table reads are blocked.