MS SQL bulk user and database creation

Question

I'm exploring options to setup a SQL 2016 database server to support teaching students database fundamentals. There will be close to 200 students on the course.

I'd like to automate the creation of all the user accounts and associated database via a script - this means the database for each user needs to be created at the same time. Basically each student's user login name and database will be named as the student's ID. Login authentication will be SQL authentication, not Windows as the lab PCs are not on AD.

If anyone could point me in the direction of any scripts I could modify or use it would be extremely helpful - basically if I go down this route I'll need to do it every year so scripting would be optimal.

Thanks D.

Answer 1

Using Dynamic Sql and using while loop we can loop the student names and create database and logins.Hope it helps you

IF OBJECT_ID('Tempdb..#USerTable') IS NOT NULL
    DROP TABLE #USerTable

IF OBJECT_ID('Tempdb..#USerNameINSERT') IS NOT NULL
    DROP TABLE #USerNameINSERT

IF OBJECT_ID('Tempdb..#PasswordsINSERT') IS NOT NULL
    DROP TABLE #PasswordsINSERT

--ADD Your student Names Passwords in a single  line as below
DECLARE @Usernames NVARCHAR(max) = 'Stud1,Stud2,Stud3,Stud4'
    ,@Password NVARCHAR(max) = 'Password1,Password2,Password3,Password4'
    ,@Usernameselect NVARCHAR(max)
    ,@DatabaseNameselect NVARCHAR(max)
    ,@PasswordSelect NVARCHAR(max)
    ,@Usernameselect1 NVARCHAR(max)
    ,@DatabaseNameselect1 NVARCHAR(max)
    ,@PasswordSelect1 NVARCHAR(max)


DECLARE @USerName TABLE (
    ID INT IDENTITY
    ,Usernames NVARCHAR(max)
    ,Passwords NVARCHAR(max)
    )

INSERT INTO @USerName (
    UserNAmes
    ,Passwords
    )
SELECT @Usernames
    ,@Password


SELECT ID,UserNAmes INTO #USerNameINSERT
FRom  (
SELECT Row_number()Over(Order by (SELECT NULL)) AS ID,Split.a.value('.', 'VARCHAR(1000)') AS UserNAmes
            FROM (
                SELECT ID, CAST('<S>' + REPLACE(UserNAmes, ',', '</S><S>') + '</S>' AS XML) AS UserNAmes
                FROM @USerName
                ) AS A
            CROSS APPLY UserNAmes.nodes('/S') AS Split(a)
            )DT

SELECT ID,Passwords  INTO  #PasswordsINSERT
FRom (
SELECT  Row_number()Over(Order by (SELECT NULL)) AS ID,Split.a.value('.', 'VARCHAR(1000)') AS Passwords
            FROM (
                SELECT CAST('<S>' + REPLACE(Passwords, ',', '</S><S>') + '</S>' AS XML) AS Passwords
                FROM @USerName
                ) AS A
            CROSS APPLY Passwords.nodes('/S') AS Split(a)

)DT

SELECT u.ID
    ,u.UserNAmes
    ,u.UserNAmes+'Db' AS DatabaseName
    ,p.Passwords
INTO #USerTable
FROM #USerNameINSERT u
INNER JOIN #PasswordsINSERT p ON p.ID = u.ID


DECLARE @minId INT
    ,@maxId INT
    ,@SqlQuery NVARCHAR(max)
    ,@SqlQuery1 NVARCHAR(max)
    ,@SqlQuery2 NVARCHAR(max)

SELECT @minId = MIN(ID)
    ,@maxId = Max(Id)
FROM #USerTable

WHILE (@minId <= @maxId)
BEGIN
    SELECT @Usernameselect = UserNAmes
        ,@DatabaseNameselect = DatabaseName
        ,@PasswordSelect = Passwords
    FROM #USerTable
    WHERE ID = @minId

    SET @SqlQuery = 'CREATE DATABASE ' + @DatabaseNameselect
    SET @SqlQuery1 = 'USE [' + @DatabaseNameselect + ']'
    SET @SqlQuery2 = 'CREATE LOGIN ' + @Usernameselect + ' WITH PASSWORD=N''' + @PasswordSelect + ''' MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON'+ CHAR(13) + CHAR(10) + 'GO'
    SET @SqlQuery = ISNULL('', 'GO') + CHAR(13) + CHAR(10) + @SqlQuery 
                    + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + @SqlQuery1 
                    + CHAR(13) + CHAR(10) + 'Go' + + CHAR(13) + CHAR(10) + @SqlQuery2

    --EXEC (@SqlQuery)

    PRINT @SqlQuery

    SET @minId = @minId + 1
END