SQL for .NET developers Intermediate

Logins, users and permissions

Database security decides who can connect and what each account may do. This lesson explains logins and users, roles, granting only what an app needs, schemas as permission boundaries, and safer options for connecting from .NET such as managed identities.

Who can do what
  1. LoginCan connect to the server
  2. Database userMapped to the login inside one database
  3. RoleA group of rights, such as db_datareader
  4. PermissionGRANT, DENY or REVOKE on a table, schema or procedure

Logins and users

A login lets someone connect to the server. A user maps that login into one database and holds the permissions there. One login can have a user in several databases with different rights.

Azure SQL also supports contained users, which live only in the database, and Microsoft Entra ID users and managed identities, which need no password at all.

Least privilege for an app

The web app should not connect as sa or as db_owner. Give it its own user with only the rights it needs. If an attacker finds an injection bug, they get only those rights.

SQL
CREATE LOGIN ShopApp WITH PASSWORD = '<strong password from a secret store>';
USE Shop;
CREATE USER ShopApp FOR LOGIN ShopApp;

-- Read and write data, nothing else
ALTER ROLE db_datareader ADD MEMBER ShopApp;
ALTER ROLE db_datawriter ADD MEMBER ShopApp;
GRANT EXECUTE ON SCHEMA::dbo TO ShopApp;

-- A separate account runs migrations with db_ddladmin
-- Reports get read-only access to one schema
GRANT SELECT ON SCHEMA::reporting TO ReportsUser;

Useful building blocks

  • Fixed roles: db_datareader, db_datawriter, db_ddladmin and db_owner. Prefer custom roles for fine-grained rights.
  • GRANT gives a right, DENY blocks it even if a role grants it, REVOKE removes an earlier GRANT or DENY.
  • Schemas group objects so you can grant on a whole schema at once.
  • Row-level security filters rows per user; dynamic data masking hides parts of values such as card numbers.

Connecting from .NET without passwords

On Azure, a managed identity lets App Service sign in to Azure SQL with no secret in the connection string.

C#
// appsettings.json (no password anywhere)
"ConnectionStrings": {
  "Shop": "Server=tcp:myserver.database.windows.net;Database=Shop;Authentication=Active Directory Default;Encrypt=True"
}
// In the database, once:
// CREATE USER [my-app-service] FROM EXTERNAL PROVIDER;
// ALTER ROLE db_datareader ADD MEMBER [my-app-service];

Interview question

Why should a web app not connect to the database as db_owner?

If the app is compromised, for example through SQL injection, the attacker gets every right the app's account has. As db_owner that includes dropping tables and changing permissions. With least privilege, such as only db_datareader, db_datawriter and EXECUTE, the damage is limited. Schema changes should run under a separate account used only by deployments.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.