---

declare @SQL NVARCHAR(MAX) = '';
DECLARE @COUNTUSER INT = 1;

SET @SQL = 'CREATE DATABASE BASEALL;'
EXEC(@SQL);--1

SET @SQL = 'USE BASEALL; CREATE TABLE USERSALL(
USERID INT IDENTITY PRIMARY KEY,
USERNAME NVARCHAR(100) NOT NULL,
USERPASSWORD NVARCHAR(512) NOT NULL);'
EXEC(@SQL);--2

WHILE @COUNTUSER <= 10
BEGIN
	DECLARE @USERNAME NVARCHAR(100) = 'M' + CAST(@COUNTUSER AS NVARCHAR(2));
	DECLARE @DBNAME NVARCHAR(50) = 'BASE' + CAST(@COUNTUSER AS NVARCHAR(2));
	DECLARE @USERPASSWORD NVARCHAR(512) = '';

	SELECT @USERNAME AS [USER NAME]

	DECLARE @SYMBOLCOUNTER INT = 1;
	DECLARE @CHARS NVARCHAR(50) = 'QWERTYUIOPASDFGHJKLZXCVBNM0123456789';
	DECLARE @LENGTH INT = 4;

	WHILE @SYMBOLCOUNTER <= @LENGTH
	BEGIN
		SET @USERPASSWORD = @USERPASSWORD + SUBSTRING(@CHARS, CAST(RAND() * LEN(@CHARS) AS INT) + 1, 1);
		SET @SYMBOLCOUNTER = @SYMBOLCOUNTER + 1;
	END;

	SET @SQL = 'USE BASEALL; INSERT INTO USERSALL(USERNAME, USERPASSWORD)
	VALUES(' + QUOTENAME(@USERNAME, '''') + ', ' + QUOTENAME(@USERPASSWORD, '''') + ');'
	EXEC(@SQL);--3

	SET @SQL = 'CREATE DATABASE ' + QUOTENAME(@DBNAME) + ';';
	EXEC(@SQL);--4

	SET @SQL = 'CREATE LOGIN ' + QUOTENAME(@USERNAME) + ' WITH PASSWORD = ' + QUOTENAME(@USERPASSWORD, '''') + ', CHECK_POLICY = OFF;';
	EXEC(@SQL); --5

	SET @SQL = 'USE ' + QUOTENAME(@DBNAME) + '; CREATE USER ' + QUOTENAME(@USERNAME) + ' FOR LOGIN ' + QUOTENAME(@USERNAME) + ';'
	EXEC(@SQL); --6

	SET @SQL = 'USE ' + QUOTENAME(@DBNAME) + '; ALTER ROLE db_datareader add member ' + QUOTENAME(@USERNAME) + '; ALTER ROLE db_datawriter add member ' + QUOTENAME(@USERNAME) + ';'
	exec(@sql); --7

	set @COUNTUSER = @COUNTUSER + 1;
	select @USERPASSWORD as [user pass]
	end;


Go
USE BaseAll
Go
UPDATE UsersAll
SET USERPASSWORD = CONVERT(VARBINARY(MAX), ENCRYPTBYPASSPHRASE('pass_phrase', USERPASSWORD))
PRINT('Пароли успешно зашифрованы:')
SELECT * FROM UsersAll


Go
USE BaseAll
Go
SELECT USERID, 
	   USERNAME, 
	   CONVERT(NVARCHAR(50), DECRYPTBYPASSPHRASE('pass_phrase', USERPASSWORD))USERPASSWORD 
FROM UsersAll

USE baseall;
GO
BACKUP DATABASE baseall
TO DISK = 'c:\q\bd.bak'-- тут путь поменять!!!

use [master]
RESTORE DATABASE [baseall]
FROM DISK = 'c:\q\bd.bak' -- тут путь такой же как в бэкап!!!