Programmatically give access to databases

Question

Scenario: Instructor of a database course wants to do the following for each of 100 students every semester: (a) create login from windows, (b) create database for each student where they are db_owner, (c) each student has read access to a database. Here is the script I have so far:

DECLARE @DBname varchar(10) = 'aa1833'
DECLARE @Lname varchar(20) = 'STUDENTS'+'\'+@DBname

DECLARE @CreateDB varchar(max) = 'CREATE DATABASE '+@DBname
EXEC(@CreateDB)

DECLARE @CreateLogin varchar(max) = 'CREATE LOGIN ['+@Lname+ '] FROM WINDOWS'
EXEC(@CreateLogin)

USE sample_database
EXEC sp_addrolemember 'db_datareader', @Lname
EXEC sp_droprolemember 'db_owner', @Lname

DECLARE @dbRights varchar(max) = 'USE '+@DBname
EXEC(@dbRights)
EXEC sp_addrolemember 'db_owner', @Lname

I would appreciate help with following issues:

1. The above script is able to create logins and user's database. However, access rights (to individual database and sample database) are incorrect.
2. To manage server space, I would like to delete student accounts and their databases at the end of each semester. Can we have database names such as students\f21\001\aa1833?
3. Any other comments to improve this set up (students cannot access others' work and edit contents) would be appreciated.

SQL Server 15

Answer 1

The database context reverts back to the outer context (sample_database) after the dynamic SQL USE statement runs so the subsequent sp_addrolemember runs in sample_database instead of aa1833 as intended.

Execute USE and sp_addrolemember in the same dynamic SQL batch to avoid the issue:

DECLARE @dbRights varchar(max) = 'USE ' + QUOTENAME(@DBname) + N';EXEC sp_addrolemember ''db_owner'', @Lname';
sp_executesql @dbRights, N'@Lname sysname', @Lname = @Lname;