SQL Security

In simple words#

Learning objectives#

• Distinguish logins, users and roles
• Apply least privilege with roles and GRANT
• Prevent SQL injection
• Write safe dynamic SQL

Explanation#

A login authenticates to the server; a user maps that login into a database. Permissions are best granted to roles, and users are added to roles.

Least privilege: applications should get only what they need — often EXECUTE on stored procedures or SELECT/INSERT on specific schemas — never sysadmin or db_owner.

SQL injection happens when user input is concatenated into SQL text. Parameterised queries and procedures send values separately, so input is never executed as code.

When dynamic SQL is unavoidable, use sp_executesql with parameters and QUOTENAME for identifiers such as column names.

Code example: A least-privilege app account and safe dynamic SQL#

1CREATE LOGIN shop_app WITH PASSWORD = 'Use-A-Long-Random-Secret-1!';
2CREATE USER shop_app FOR LOGIN shop_app;
3CREATE ROLE app_role;
4GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO app_role;
5GRANT EXECUTE ON SCHEMA::dbo TO app_role;
6ALTER ROLE app_role ADD MEMBER shop_app;
7

8-- Safe dynamic sort column + parameterised value
9DECLARE @sortColumn SYSNAME = N'Price', @category NVARCHAR(30) = N'Furniture';
10IF @sortColumn NOT IN (N'Name', N'Price') THROW 50000, 'Invalid sort column', 1;
11DECLARE @sql NVARCHAR(MAX) =
12  N'SELECT Name, Price FROM dbo.Products WHERE Category = @cat ORDER BY ' + QUOTENAME(@sortColumn) + N';';
13EXEC sp_executesql @sql, N'@cat NVARCHAR(30)', @cat = @category;

Name   Price
Chair  4500.00
Desk   12000.00

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

1. The app gets its own login and database user.
2. Permissions go to a role, and the user joins the role — easy to audit and change.
3. The sort column is checked against an allow-list and wrapped with QUOTENAME.
4. The category value is passed as a parameter to sp_executesql, so it can never become SQL code.

Real-world use case#

Common mistakes#

Best practices#

• Grant permissions to roles, not individuals
• Give apps the minimum permissions they need
• Parameterise every query; use sp_executesql for dynamic SQL
• Encrypt connections and back up regularly; consider Always Encrypted for sensitive columns

Remember this#

Practice exercise#

1CREATE ROLE reporting_reader;
2GRANT SELECT ON SCHEMA::reporting TO reporting_reader;

Mini quiz#

Want more? Practise all SQL Server MCQs · SQL Server interview questions

Related topics#

My notes#

Saved in this browser.