Блог

Сценарий: Преподаватель курса по базам данных хочет выполнять следующие действия для каждого из 100 студентов каждый семестр: (а) создать учетную запись из Windows, (б) создать базу данных для каждого студента, где они являются владельцами базы данных, (в) каждый студент имеет доступ для чтения к базе данных. Вот сценарий, который у меня есть до сих пор:

 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

 

Я был бы признателен за помощь в решении следующих вопросов:

1. Приведенный выше скрипт способен создавать логины и базу данных пользователей. Однако права доступа (к отдельной базе данных и образцу базы данных) неверны.
2. Для управления пространством сервера я хотел бы удалять учетные записи студентов и их базы данных в конце каждого семестра. Можем ли мы иметь имена баз данных, такие как studentsf2101aa1833?
3. Любые другие комментарии для улучшения этой настройки (учащиеся не могут получать доступ к чужой работе и редактировать содержимое) будут оценены.

SQL Server 15

Контекст базы данных возвращается обратно во внешний контекст ( sample_database ) после выполнения динамической USE инструкции SQL, поэтому последующие sp_addrolemember запуски sample_database выполняются aa1833 не так, как предполагалось.

Выполните USE и sp_addrolemember в том же динамическом пакете SQL, чтобы избежать этой проблемы:

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