INFRASTRUCTURE LEARNINGINFRASTRUCTURE LEARNING
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
Infrastructure learning guide
Find a lesson or lab/

Esc to close · Search terms stay on your device

LEARNING日本語

Topic lessonTopic: SQL Server and database foundations

SQL Server access: logins, users, roles and permissions

A login is a server-level authentication principal, a database user is a principal within a database, and a role groups permissions. Successful authentication does not imply access to every database or table. Follow a CorpDocs document read to separate principal scope and SELECT permission.

What you will learn

Distinguish login, database user, role and permission. Explain read-only access without conflating AWS IAM API permissions with database rights.

Prerequisite

Know the SSMS server and current database. The example uses fictional SQL Server authentication without creating users or entering passwords.

Logins and database users have different scopes

Identify where each principal is defined
TypeScopeOur example
LoginSQL Server instanceAuthenticate as reader_demo
Database userTarget databasereader_demo within CorpDocs
Database roleTarget databaseExample membership in document_readers
PermissionSecurable and operationSELECT on app.Documents

This example maps a server login to a CorpDocs database user. It does not assume a matching user or grant in TrainingDocs. Other authentication methods, including Windows and Microsoft Entra, have their own prerequisites.

Connect role membership to object permissions

Mapping, membership and grants are not a network path
  1. login: reader_demo

    A principal authenticating to the instance.

    Database mapping →
  2. user: reader_demo

    Mapped into CorpDocs in this example.

    Role membership →
  3. role: document_readers

    Membership in a database read role is stipulated.

    Object permission →
  4. SELECT

    Granted on the two required tables, Documents and People.

AWS IAM / API

AWS management API permissions are separate from SELECT inside this database.

A fictional teaching example. Containment, communication and permission relationships have different meanings.

In the role example, document_readers has SELECT on the two tables and reader_demo is a member. Other examples can use direct grants. Role membership does not inherently mean administrator status. Evaluate the permissions applicable to the principal.

Separate required reads from excessive administration
NeedStipulated permissionExcessive for this need
Read documents and peopleSELECT on two required tablesdb_owner in all DBs or server sysadmin
Change status in a separate exampleeditor_demo has SELECT and UPDATE(status)Elevating reader_demo to full administration
Use another databaseEstablish its user and rights separatelyAssuming equal rights from equal names

Check target and operation even when names match

Stipulated rights and conditions not established
CaseTarget and operationConclusion
S0 / reader_demoSELECT CorpDocs.app.DocumentsWithin the explicitly stipulated read grant
S0 / reader_demoUPDATE the same tableNot granted in this example
S1 / reader_demoSELECT TrainingDocs.app.DocumentsDatabase access and SELECT unspecified
W0 / editor_demoUPDATE status in a fresh T0 copyA separate case with the required rights stipulated

If a table cannot be read, distinguish user mapping, object existence, current database and permission. An unspecified-rights example does not predict one inevitable error message.

AWS IAM API permission is not a database login

Permission to call an AWS management API is distinct from SQL Server authentication and SELECT. Being allowed to inspect DB-instance management information does not itself establish a database principal and table rights.

Ordinary RDS for SQL Server does not provide the sysadmin role. An administrator-like account name does not make ordinary RDS, RDS Custom and EC2 management boundaries identical. RDS-specific configuration is a later topic.

Check authentication and authorization scopes

Choose an option to see the result and explanation.

Does a valid SQL Server login allow SELECT in every database?
Read the answer and reason

Answer: No; check the database user and permissions · Server authentication and database authorization are distinct.

Does AWS IAM permission automatically authenticate a user to RDS SQL Server?
Read the answer and reason

Answer: No; database authentication is distinct · IAM API rights are not SQL Server login credentials.

What is suitable for an app needing only SELECT?
Read the answer and reason

Answer: SELECT restricted to the required tables · Least-privilege table access matches the requirement.

Check, then explain in your own words

  1. Identify the scope of a login and database user.
  2. Explain membership in a role that grants SELECT.
  3. Keep the S0 reader and W0 editor separate.
  4. Do not turn unspecified access to another DB into a grant or denial.
Check your understanding: Does an app needing only reads require db_owner?

For this requirement, consider SELECT on the required tables.

Match permission scope to the required work. Investigate identity and target-database access rather than using excessive privileges to mask a connection problem.

Sources and conditions checked

Primary references are collected here with their scope, conditions and corresponding sections. Independent expert review and live-environment validation have not been performed.