Security: logins, roles, permissions and SQL injection

So far you have connected as yourself, probably as an administrator who can do anything. A real database is used by an application, by report writers and by support staff, and each should be able to do only what they need. This lesson sets that up, then looks at the most common way databases are attacked: SQL injection.

Logins and users

SQL Server checks identity in two places:

• A login lets someone connect to the server. It is either a Windows account or group (CREATE LOGIN [SHOP\Ana] FROM WINDOWS) or a SQL Server login with its own password.
• A user gives a login access to one database. A login with no user in BuildMart can connect to the server but cannot use BuildMart.

1USE master;2CREATE LOGIN shop_web WITH PASSWORD = N'use-a-long-random-password-here';3GO4USE BuildMart;5CREATE USER shop_web FOR LOGIN shop_web;

Windows logins are preferred: there is no password in a connection string, and when someone leaves, disabling their Windows account locks them out of SQL Server too. To try permissions in this lesson without creating logins on your server, use users without a login. They exist only inside the database and are made for exactly this kind of test:

1CREATE USER Ana WITHOUT LOGIN;      -- works in the shop2CREATE USER Raj WITHOUT LOGIN;      -- writes reports3 4SELECT name, type_desc, authentication_type_desc5FROM sys.database_principals6WHERE name IN ('Ana', 'Raj');

name  type_desc  authentication_type_desc
----  ---------  ------------------------
Ana   SQL_USER   NONE
Raj   SQL_USER   NONE

GRANT, and testing with EXECUTE AS

A new user can do almost nothing. GRANT gives a permission on an object. EXECUTE AS USER lets you, as an administrator, run statements as if you were that user, and REVERT switches back:

1GRANT SELECT ON dbo.Products TO Ana;2 3EXECUTE AS USER = 'Ana';4SELECT USER_NAME() AS RunningAs, COUNT(*) AS Products FROM dbo.Products;5SELECT COUNT(*) AS Customers FROM dbo.Customers;6REVERT;

RunningAs  Products
---------  --------
Ana              12

Msg 229, Level 14, State 5, Line 5
The SELECT permission was denied on the object 'Customers', database 'BuildMart', schema 'dbo'.

Ana can read Products but not Customers: Msg 229, permission denied. The error names the object and the permission, which makes it easy to see what is missing. Permissions can be granted at several levels:

Roles

Granting to people one by one is hard to keep track of. Grant to a role instead, and add people to the role. When Raj moves to another team, you remove him from one role rather than hunting for his permissions:

1CREATE ROLE Reporting;2GRANT SELECT ON SCHEMA::dbo TO Reporting;3DENY SELECT ON dbo.Customers (Email, Phone) TO Reporting;4ALTER ROLE Reporting ADD MEMBER Raj;5 6EXECUTE AS USER = 'Raj';7SELECT TOP (2) FirstName, City FROM dbo.Customers ORDER BY CustomerId;8SELECT TOP (2) FirstName, Email FROM dbo.Customers ORDER BY CustomerId;9REVERT;

FirstName  City
---------  -----
Asha       Leeds
Ben        York

Msg 230, Level 14, State 1, Line 8
The SELECT permission was denied on the column 'Email' of the object 'Customers', database 'BuildMart', schema 'dbo'.

Raj can read every table in dbo, but the customers’ email addresses and phone numbers are off limits (Msg 230 is the column-level form of “permission denied”). DENY always wins: it beats any GRANT the user gets from another role. REVOKE is different: it removes a GRANT or DENY, going back to “not mentioned”.

SQL Server has built-in roles too. In each database, db_datareader can read every table and db_datawriter can change every table; db_owner can do anything in the database. At server level, sysadmin can do anything at all. Use them sparingly: an application that connects as db_owner or sysadmin turns every bug into a disaster.

1SELECT r.name AS RoleName, m.name AS Member2FROM sys.database_role_members AS rm3JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id4JOIN sys.database_principals AS m ON m.principal_id = rm.member_principal_id5WHERE m.name IN ('Ana', 'Raj');6 7SELECT pr.name AS Grantee, pe.state_desc, pe.permission_name,8       COALESCE(OBJECT_NAME(pe.major_id), pe.class_desc) AS OnObject,9       COL_NAME(pe.major_id, pe.minor_id) AS OnColumn10FROM sys.database_permissions AS pe11JOIN sys.database_principals AS pr ON pr.principal_id = pe.grantee_principal_id12WHERE pr.name IN ('Ana', 'Raj', 'Reporting')13ORDER BY Grantee, OnObject, OnColumn;

RoleName   Member
---------  ------
Reporting  Raj

Grantee    state_desc  permission_name  OnObject   OnColumn
---------  ----------  ---------------  ---------  --------
Ana        GRANT       CONNECT          DATABASE   NULL
Ana        GRANT       SELECT           Products   NULL
Raj        GRANT       CONNECT          DATABASE   NULL
Reporting  DENY        SELECT           Customers  Email
Reporting  DENY        SELECT           Customers  Phone
Reporting  GRANT       SELECT           SCHEMA     NULL

Procedures as the front door: ownership chaining

The safest way to let an application change data is to give it no table permissions at all, only EXECUTE on procedures that do the work. This works because of ownership chaining: when a procedure owned by dbo reads a table owned by dbo, SQL Server does not check the caller’s permissions on the table. Owning the procedure and the table is enough.

1CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders @CustomerId int2AS3SELECT o.OrderId, o.OrderDate, o.Status4FROM dbo.Orders AS o5WHERE o.CustomerId = @CustomerId6ORDER BY o.OrderId;7GO8GRANT EXECUTE ON dbo.GetCustomerOrders TO Ana;9 10EXECUTE AS USER = 'Ana';11EXEC dbo.GetCustomerOrders @CustomerId = 1;12SELECT COUNT(*) FROM dbo.Orders;13REVERT;

OrderId  OrderDate   Status
-------  ----------  -------
      1  2024-02-03  Shipped
      3  2024-03-01  Shipped

Msg 229, Level 14, State 5, Line 5
The SELECT permission was denied on the object 'Orders', database 'BuildMart', schema 'dbo'.

Ana can see customer 1’s orders through the procedure but cannot read Orders directly. She can only do what the procedure does, with the checks the procedure makes. Views work the same way: grant SELECT on a view and the caller can read through it without any permission on the tables underneath.

SQL injection

SQL injection happens when text typed by a user is glued into a SQL statement, so the user can change the statement itself. Here is a search procedure written the dangerous way, building the query as a string:

1CREATE OR ALTER PROCEDURE dbo.FindCustomers @LastName nvarchar(100)2AS3BEGIN4    DECLARE @sql nvarchar(max) =5        N'SELECT CustomerId, FirstName, LastName, City FROM dbo.Customers ' +6        N'WHERE LastName = ''' + @LastName + N'''';7    PRINT @sql;8    EXEC (@sql);9END;10GO11EXEC dbo.FindCustomers @LastName = N'Patel';

SELECT CustomerId, FirstName, LastName, City FROM dbo.Customers WHERE LastName = 'Patel'

CustomerId  FirstName  LastName  City
----------  ---------  --------  -----
         1  Asha       Patel     Leeds

It works for honest input. The PRINT shows the statement that ran. Now an attacker types a quote, a condition that is always true, and -- to comment out the rest:

1EXEC dbo.FindCustomers @LastName = N'x'' OR 1 = 1 --';

SELECT CustomerId, FirstName, LastName, City FROM dbo.Customers WHERE LastName = 'x' OR 1 = 1 --'

CustomerId  FirstName  LastName  City
----------  ---------  --------  ---------
         1  Asha       Patel     Leeds
         2  Ben        Carter    York
         3  Chloe      Evans     Leeds
         4  Dev        Singh     Bradford
         5  Ella       Brown     NULL
         6  Farid      Khan      York
         7  Grace      Lee       Leeds
         8  Zoë        Müller    Harrogate

The quote closed the string early, and the rest of the input became SQL: OR 1 = 1 matched every customer. With UNION the attacker can read any other table the account can see, here the customers’ private contact details:

1EXEC dbo.FindCustomers2    @LastName = N'x'' UNION SELECT CustomerId, Email, Phone, NULL FROM dbo.Customers --';

SELECT CustomerId, FirstName, LastName, City FROM dbo.Customers WHERE LastName = 'x' UNION SELECT CustomerId, Email, Phone, NULL FROM dbo.Customers --'

CustomerId  FirstName          LastName       City
----------  -----------------  -------------  ----
         1  asha@example.com   0113 496 0001  NULL
         2  NULL               01904 555 010  NULL
         3  chloe@example.com  NULL           NULL
         4  dev@example.com    01274 555 220  NULL
         5  ella@example.com   NULL           NULL
         6  farid@example.com  01904 555 777  NULL
         7  NULL               NULL           NULL
         8  zoe@example.com    01423 555 123  NULL

And if the account may change data, the input can contain a whole new statement:

1EXEC dbo.FindCustomers @LastName = N'x''; UPDATE dbo.Products SET Price = 0.01; --';2 3SELECT TOP (3) Name, Price FROM dbo.Products ORDER BY ProductId;

SELECT CustomerId, FirstName, LastName, City FROM dbo.Customers WHERE LastName = 'x'; UPDATE dbo.Products SET Price = 0.01; --'

CustomerId  FirstName  LastName  City
----------  ---------  --------  ----

Name              Price
----------------  -----
Claw hammer        0.01
Cordless drill     0.01
Tape measure 8 m   0.01

Every product now costs a penny. Injection also breaks innocent input: a customer called O’Brien gets an error, because the quote in the name ends the string. That error is often how attackers find the weakness in the first place.

SELECT CustomerId, FirstName, LastName, City
FROM dbo.Customers
WHERE LastName = 'x' OR 1 = 1 --'

The fix: parameters

Never put input into the SQL text. Pass it as a parameter: the statement is fixed, and the value travels separately, so it can only ever be a value, never SQL. In T-SQL, sp_executesql takes the statement, the parameter declarations and the values:

1CREATE OR ALTER PROCEDURE dbo.FindCustomers @LastName nvarchar(100)2AS3BEGIN4    EXEC sp_executesql5        N'SELECT CustomerId, FirstName, LastName, City6          FROM dbo.Customers WHERE LastName = @LastName',7        N'@LastName nvarchar(100)',8        @LastName = @LastName;9END;10GO11EXEC dbo.FindCustomers @LastName = N'Patel';12EXEC dbo.FindCustomers @LastName = N'x'' OR 1 = 1 --';13EXEC dbo.FindCustomers @LastName = N'O''Brien';

CustomerId  FirstName  LastName  City
----------  ---------  --------  -----
         1  Asha       Patel     Leeds

CustomerId  FirstName  LastName  City
----------  ---------  --------  ----

CustomerId  FirstName  LastName  City
----------  ---------  --------  ----

Patel is found; the attack is just an odd surname that matches nobody; O’Brien works without an error. Of course, this search needs no dynamic SQL at all: a plain SELECT … WHERE LastName = @LastName in the procedure is parameterized already. Use dynamic SQL only when the shape of the query must change.

When the SQL itself must change: QUOTENAME and allow-lists

Parameters can hold values, not names: you cannot pass a column to sort by as a parameter. When a name must go into the SQL text, check it against a list of allowed names and wrap it in QUOTENAME, which puts it in square brackets and doubles any ] inside it, so it can only ever be read as a name:

1CREATE OR ALTER PROCEDURE dbo.ListProducts @SortBy sysname2AS3BEGIN4    IF @SortBy NOT IN (N'Name', N'Price', N'Stock')5        THROW 50001, 'SortBy must be Name, Price or Stock.', 1;6 7    DECLARE @sql nvarchar(max) =8        N'SELECT TOP (3) Name, Stock FROM dbo.Products ORDER BY '9        + QUOTENAME(@SortBy) + N' DESC';10    EXEC sp_executesql @sql;11END;12GO13EXEC dbo.ListProducts @SortBy = N'Stock';14EXEC dbo.ListProducts @SortBy = N'Stock; DROP TABLE dbo.Orders';

Name              Stock
----------------  -----
Timber 2x4 2.4 m    500
Cement 25 kg        300
Sand 25 kg          220

Msg 50001, Level 16, State 1, Procedure dbo.ListProducts, Line 5
SortBy must be Name, Price or Stock.

Least privilege

Defence in depth means planning for the bug you have not found yet. If the shop’s web application connects as a user that can only EXECUTE a set of procedures, an injection flaw somewhere in it cannot drop tables or read the customer list directly. A sensible starting point:

• One login per application, never sa or a sysadmin; Windows or managed identities where possible, so there is no password to leak.
• Applications get EXECUTE on their procedures (or SELECT on views), not db_owner.
• People get roles that match their jobs; DENY the sensitive columns.
• Every query built from input is parameterized: in procedures, and in application code (the next lesson shows SqlParameter in C#).
• Names that must be dynamic are checked against an allow-list and wrapped in QUOTENAME.

1CREATE ROLE ShopStaff;2GRANT EXECUTE ON dbo.GetCustomerOrders TO ShopStaff;3GRANT SELECT ON dbo.Products TO ShopStaff;4 5CREATE USER Lee WITHOUT LOGIN;6ALTER ROLE ShopStaff ADD MEMBER Lee;7 8EXECUTE AS USER = 'Lee';9EXEC dbo.GetCustomerOrders @CustomerId = 4;10SELECT COUNT(*) FROM dbo.Customers;11REVERT;

OrderId  OrderDate   Status
-------  ----------  -------
      4  2024-03-15  Shipped
      7  2024-05-05  Shipped

Msg 229, Level 14, State 5, Line 10
The SELECT permission was denied on the object 'Customers', database 'BuildMart', schema 'dbo'.