What you will learn
Logins authenticate at the instance, database users establish database identities, schemas group objects, and roles collect permissions. This lab uses a user without a login so permission behavior can be tested without embedding credentials. Modern SQL Server also supports user-defined server roles; the old restriction in the training slides no longer applies.
Before you begin
Use an isolated SQL Server 2022 Developer lab on host1.sample.com and a current SQL Server Management Studio client. Developer edition is for nonproduction use. Connect with a dedicated lab administrator for setup; application access is tested separately. The examples use TutorialDB and synthetic rows only. GO is a client batch separator, not a Transact-SQL statement. Sample paths are on the database server, not your workstation. TutorialDB must exist. Use a fresh database for these named lab objects.
1. Create a schema and fixture
USE TutorialDB;
GO
CREATE SCHEMA app AUTHORIZATION dbo;
GO
CREATE TABLE app.AccessProbe (probe_id int NOT NULL PRIMARY KEY);
INSERT app.AccessProbe VALUES (1);
Owning a schema is different from having permission to read it. Keep ownership with dbo in this example. A production principal that owns securables can have capabilities beyond an ordinary reader.
2. Grant a focused role
CREATE ROLE app_reader AUTHORIZATION dbo;
GRANT SELECT ON SCHEMA::app TO app_reader;
CREATE USER lab_reader WITHOUT LOGIN;
ALTER ROLE app_reader ADD MEMBER lab_reader;
The schema grant applies to objects in app, including future objects. If only one table should be exposed, grant on that object instead. Do not use db_owner or sysadmin to solve a read-only application requirement. A real application would use an approved login mapped to a database user.
3. Exercise the permissions
EXECUTE AS USER = 'lab_reader';
SELECT * FROM app.AccessProbe;
SELECT HAS_PERMS_BY_NAME('app.AccessProbe','OBJECT','SELECT') AS can_select,
HAS_PERMS_BY_NAME('app.AccessProbe','OBJECT','INSERT') AS can_insert;
REVERT;
The permission query avoids deliberately failing a batch. For an additional negative test, impersonate the same user in a separate window and attempt an INSERT, then REVERT even after the error. Never leave an administration session in an unintended impersonation context.
4. Read the example output
The following is illustrative, normalized output, not a transcript from a customer system. Values depend on your release and lab state.
probe_id
1
can_select can_insert
1 0
These values assume a clean lab with no extra permissions granted through public or other roles.
Verify the result
Confirm a reader can select the fixture but cannot insert or change its schema. Check USER_NAME() after REVERT to verify the original database context. Repeat the access test after any role change.
Troubleshooting
Unexpected write access can come from additional role memberships, ownership, or public grants. Inspect effective permissions instead of adding a DENY blindly. A server login alone does not automatically create a user in every database.
Recovery and next steps
Undo this lab by removing the role membership and dropping the lab user and role after confirming they own no objects. Keep the fixture if needed for later exercises. Record an application permission matrix before granting broader access.