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.
Distinguish login, database user, role and permission. Explain read-only access without conflating AWS IAM API permissions with database rights.
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
| Type | Scope | Our example |
|---|---|---|
| Login | SQL Server instance | Authenticate as reader_demo |
| Database user | Target database | reader_demo within CorpDocs |
| Database role | Target database | Example membership in document_readers |
| Permission | Securable and operation | SELECT 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
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.
| Need | Stipulated permission | Excessive for this need |
|---|---|---|
| Read documents and people | SELECT on two required tables | db_owner in all DBs or server sysadmin |
| Change status in a separate example | editor_demo has SELECT and UPDATE(status) | Elevating reader_demo to full administration |
| Use another database | Establish its user and rights separately | Assuming equal rights from equal names |
Check target and operation even when names match
| Case | Target and operation | Conclusion |
|---|---|---|
| S0 / reader_demo | SELECT CorpDocs.app.Documents | Within the explicitly stipulated read grant |
| S0 / reader_demo | UPDATE the same table | Not granted in this example |
| S1 / reader_demo | SELECT TrainingDocs.app.Documents | Database access and SELECT unspecified |
| W0 / editor_demo | UPDATE status in a fresh T0 copy | A 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.
Check, then explain in your own words
- Identify the scope of a login and database user.
- Explain membership in a role that grants SELECT.
- Keep the S0 reader and W0 editor separate.
- 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.
- Principals (Database Engine)Checked:
Scope and conditionsServer-level and database-level principals have different scopes. Applies to the stated SQL Server example; no live-engine test.
Used in this lessonLogins and database users have different scopes Connect role membership to object permissions AWS IAM API permission is not a database login Check authentication and authorization scopes
- What is IAM?Checked:
Scope and conditionsIdentity and access concepts
Used in this lessonAWS IAM API permission is not a database login
Scope and conditionsOrdinary RDS for SQL Server does not provide sysadmin. RDS Custom and self-managed deployments have different responsibilities.
Used in this lessonAWS IAM API permission is not a database login Check authentication and authorization scopes