Logins, users and permissions

With Entra sign-in working, Elena turns to the question that matters more than how people get in: what they can do once they are in. On the old StockLine server, most staff accounts were members of db_datareader, the application login was db_owner, and two developers from a 2019 project were still in db_ddladmin. "Nobody decided that," Walt admits. "It accumulated." Elena's goal for the test environments is the principle of least privilege: every principal gets exactly the permissions its job needs, granted in a way that can be read and reviewed, and nothing more.

Principals, securables and permissions

SQL Server security is built from three kinds of thing. A principal is something that can be given permissions: a login at the server or instance level, a database user, or a role at either level. A securable is something that can be protected: the server, a database, a schema, a table, a view, a procedure, a column. A permission connects them: SELECT on a table, EXECUTE on a procedure, ALTER on a schema, CONTROL on a database. The statements GRANT, DENY and REVOKE set them. GRANT allows an action, DENY forbids it and overrides any grant from another path, and REVOKE removes an earlier GRANT or DENY so that the principal falls back on whatever else applies.

Securables nest, and permissions flow down the nesting. A grant on a schema applies to every object in the schema, including objects created later. A grant on the database applies to every schema. This is why Elena prefers schema-level grants for most work: the Finch Rewards database keeps its tables in a schema called rewards and its reporting views in a schema called reporting, so a single statement can give the analysts SELECT on reporting without exposing the base tables.

Server-level and database-level roles

Roles group permissions so they can be granted to many principals at once. Managed Instance and SQL Server on VMs have the classic fixed server roles, such as sysadmin, securityadmin and serveradmin. Azure SQL Database has no sysadmin, but it offers a small set of fixed server-level roles in the virtual master database, named with a ##MS_ prefix, for jobs such as reading server state, managing logins or defining database-level settings. They let a monitoring account read performance information for every database on the server without being given rights inside any of them.

Every database has fixed database roles. The familiar ones are db_owner, which can do anything in the database; db_datareader and db_datawriter, which can read or change every table; db_ddladmin, which can create and alter objects; and db_securityadmin, which manages role membership and permissions. They are convenient and they are broad. db_datareader, for example, covers every table, including tables added next year that might hold personal information. For that reason, Elena uses fixed database roles only for administrators and creates user-defined roles for everyone else.

Designing least-privilege access

Hana's API does four things: it looks up a member, adds points after a purchase, redeems points for a reward and lists recent activity. Under the old model it would have connected as db_owner. Elena asks Hana to put each action in a stored procedure in a schema called api and to grant the API's managed identity EXECUTE on that schema and nothing else. The API then cannot read or change tables directly at all; it can only call procedures that do one job each. If an attacker ever controlled the API's identity, they could do only what the procedures do, and every procedure validates its inputs.

This works because of ownership chaining. When a procedure in a schema owned by dbo reads a table also owned by dbo, SQL Server does not check the caller's permission on the table, only on the procedure. The caller needs EXECUTE on the procedure, and the chain of ownership carries the rest. The chain breaks if the procedure and the table have different owners, or if the procedure builds and runs dynamic SQL, in which case the caller's own permissions are checked for the dynamic statement. Hana's procedures use parameters, not string-built SQL, which also protects them from SQL injection.

CREATE ROLE rewards_api;
GRANT EXECUTE ON SCHEMA::api TO rewards_api;
ALTER ROLE rewards_api ADD MEMBER [app-finchrewards-test];

CREATE ROLE reporting_reader;
GRANT SELECT ON SCHEMA::reporting TO reporting_reader;
ALTER ROLE reporting_reader ADD MEMBER [sg-sales-analysts];

-- Remove the broad grant made in lesson 1
ALTER ROLE db_datareader DROP MEMBER [sg-rewards-developers];

Developers still need to look at data in test. Hana's team gets a role with SELECT on the rewards schema in the test database only. In production, developers will have no standing access; a time-limited elevation through Microsoft Entra Privileged Identity Management, which activates group membership for a few hours after approval, will cover the rare cases where someone must look at live data. Sunil likes that approach because every activation is logged with a reason.

DENY, column permissions and the public role

DENY is a blunt tool. It overrides every GRANT, from any role, at any level below where it is set, except for members of db_owner and the server administrators, who bypass permission checks. Elena uses DENY sparingly, for one case: the Member table contains email addresses and dates of birth, and nobody except the API's procedures should ever read it directly. She adds a DENY SELECT on rewards.Member to the developers' role, so that even if someone later grants them SELECT on the whole schema, that table stays closed.

Permissions can also be granted on individual columns. A role can be allowed to SELECT only the MemberId, Tier and JoinDate columns of a table. Column permissions are useful but easy to lose track of; for most needs, a view that exposes only the safe columns, in a schema the role can read, is clearer. Lesson 5 shows another option for sensitive columns, dynamic data masking, which changes what a query returns without denying it.

Every database user is a member of the role public, and anything granted to public is granted to everyone. Some older applications grant permissions to public to save effort. Walt finds one in the StockLine test copy: in 2015 someone granted SELECT on a price table to public so that a kiosk application could read it. It is harmless, but it is exactly the kind of grant that nobody remembers. He moves it to a named role and records the change.

Reading effective permissions

Because permissions can come from several roles and levels at once, the question "what can this principal actually do?" needs a tool. The function fn_my_permissions lists the permissions of the current user on a securable, and EXECUTE AS lets an administrator impersonate another user to test. The catalog views sys.database_principals, sys.database_role_members and sys.database_permissions show who exists, who belongs to which role and what has been granted or denied. Walt writes one query that joins them and keeps it in his binder, renamed in the new world to a folder of scripts in the team's Git repository.

-- What can the analysts group do on the reporting schema?
EXECUTE AS USER = 'sg-sales-analysts';
SELECT permission_name FROM fn_my_permissions('reporting', 'SCHEMA');
REVERT;

-- Every explicit grant or deny in the database
SELECT pr.name AS principal, pe.state_desc, pe.permission_name,
       pe.class_desc, OBJECT_SCHEMA_NAME(pe.major_id) AS schema_name,
       OBJECT_NAME(pe.major_id) AS object_name
FROM sys.database_permissions AS pe
JOIN sys.database_principals AS pr ON pe.grantee_principal_id = pr.principal_id
WHERE pr.name NOT IN ('public', 'guest')
ORDER BY pr.name;

SQL Server Management Studio and Azure Data Studio also show permissions graphically, on the properties page of a user, role or object, and the DP-300 outline expects candidates to be able to grant permissions that way too. The graphical pages write the same GRANT and DENY statements underneath, and Walt has the habit of clicking the Script button to see the statement before running it.

Schemas as security boundaries

Schemas are more than folders for tables. Because permissions granted on a schema cover everything in it, a well-planned schema layout is also a security layout. Elena's design for Finch Rewards has four schemas with distinct audiences: rewards for the base tables, which only procedures touch; api for the procedures the application calls; reporting for views the analysts read; and security for the predicate functions of lesson 5, which nobody but administrators should change. Each schema is owned by dbo, so ownership chains stay unbroken, and each role is granted on one schema. A new table added to rewards next year is protected automatically, and a new view added to reporting is readable by analysts automatically, without anyone remembering to grant anything.

The opposite design, every object in dbo, forces object-by-object grants, and object-by-object grants are where mistakes happen. Walt finds that StockLine has 1,140 objects in dbo and 3,600 explicit object permissions, most of them granted to public or to logins of people who left years ago. Rather than translate those permissions one by one into the new world, the migration plan moves objects into a handful of schemas by purpose and grants on schemas, then checks with the impersonation query that each role can do exactly what it could before, and no more.

Application roles and the guest user

Two older features still appear in exam questions. An application role is a database principal that has no members; an application activates it with sp_setapprole and a password, and the session then takes on the role's permissions instead of the user's. It was a way to make sure users could reach data only through one program. Managed identities and procedure-based access do the same job better today, and application roles are rarely used in new designs. The guest user lets any login without a user in the database connect to it; it should stay disabled, by revoking its CONNECT permission, in every user database, and Walt adds that check to his review script.

Auditing who has access

Least privilege is not a one-time design. Elena schedules a quarterly access review: Walt exports the role membership query for each database, Sunil's team compares it with the list of people and applications that should have access, and anything unexplained is removed. Entra access reviews can do the same for the groups themselves, asking each group's owner to confirm its members. The database part and the directory part together answer the question an auditor will ask: who could read this table on a given date, and who approved it?