Создать пользователей с серверными ролями из таблицы

[zuze]

Не из таблицы у меня получается сделать.
Вот код:

Код:

USE master;
GO
CREATE DATABASE test;
GO

USE test;
GO
CREATE TABLE vxod (id INTEGER NOT NULL PRIMARY KEY IDENTITY, my_name CHAR(10) NOT NULL, my_address CHAR(20) NOT NULL, my_password CHAR(3) NOT NULL);
GO

INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Ira', 'Forumskaya', '111');
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Petya', 'Chatovskaya', '222');
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Yana', 'Zubovskaya', '333');
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Maksim', 'Ryabovo', '444');
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Roman', 'Romovskaya', '555');

CREATE LOGIN Ira WITH PASSWORD = '111';
GO
CREATE LOGIN Petya WITH PASSWORD = '222';
GO
CREATE LOGIN Yana WITH PASSWORD = '333';
GO
CREATE LOGIN Maksim WITH PASSWORD = '444';
GO
CREATE LOGIN Roman WITH PASSWORD = '555';
GO

CREATE USER Ira FOR LOGIN [Ira];
GO
CREATE USER Petya FOR LOGIN [Petya];
GO
CREATE USER Yana FOR LOGIN [Yana];
GO
CREATE USER Maksim FOR LOGIN [Maksim];
GO
CREATE USER Roman FOR LOGIN [Roman];
GO

EXEC sp_addsrvrolemember 'Ira', 'sysadmin';
GO
EXEC sp_addsrvrolemember 'Petya', 'sysadmin';
GO
EXEC sp_addsrvrolemember 'Yana', 'sysadmin';
GO
EXEC sp_addsrvrolemember 'Maksim', 'sysadmin';
GO
EXEC sp_addsrvrolemember 'Roman', 'sysadmin';
GO

А мне нужно, что бы в коде:

Код:

CREATE LOGIN Ira WITH PASSWORD = '111';
GO
CREATE LOGIN Petya WITH PASSWORD = '222';
GO
CREATE LOGIN Yana WITH PASSWORD = '333';
GO
CREATE LOGIN Maksim WITH PASSWORD = '444';
GO
CREATE LOGIN Roman WITH PASSWORD = '555';
GO

CREATE USER Ira FOR LOGIN [Ira];
GO
CREATE USER Petya FOR LOGIN [Petya];
GO
CREATE USER Yana FOR LOGIN [Yana];
GO
CREATE USER Maksim FOR LOGIN [Maksim];
GO
CREATE USER Roman FOR LOGIN [Roman];
GO

EXEC sp_addsrvrolemember 'Ira', 'sysadmin';
GO
EXEC sp_addsrvrolemember 'Petya', 'sysadmin';
GO
EXEC sp_addsrvrolemember 'Yana', 'sysadmin';
GO
EXEC sp_addsrvrolemember 'Maksim', 'sysadmin';
GO
EXEC sp_addsrvrolemember 'Roman', 'sysadmin';
GO

имена и пароли брались из таблицы vxod которую я создал, но не пойму как это сделать. Подскажите пожалуйста!

[Dimka]

Курсором по таблице в цикле.

[RXL]

А может на vxod повесить триггер AFTER INSERT и вызывать CREATE USER из него?

[zuze]

Курсором по таблице в цикле.

Попытаюсь сначала заполнить этот код:

Код:

CREATE LOGIN Ira WITH PASSWORD = '111';
GO
CREATE LOGIN Petya WITH PASSWORD = '222';
GO
CREATE LOGIN Yana WITH PASSWORD = '333';
GO
CREATE LOGIN Maksim WITH PASSWORD = '444';
GO
CREATE LOGIN Roman WITH PASSWORD = '555';
GO

Сделал так:

Код:

-- Объявление курсора и заполнение его данными
DECLARE MyCursor1 CURSOR
FOR SELECT my_name FROM vxod;
GO	

-- Открываем курсор	
OPEN myCursor1;
GO

-- Объявление курсора и заполнение его данными
DECLARE MyCursor2 CURSOR
	FOR SELECT my_password FROM vxod;
GO

-- Открываем курсор
OPEN myCursor2;
GO	

-- Перебор
WHILE @@FETCH_STATUS = 0
BEGIN
	CREATE LOGIN MyCursor1 WITH PASSWORD = MyCursor2;
	GO
	FETCH NEXT FROM MyCursor1;
	GO
	FETCH NEXT FROM MyCursor2;
	GO
END

-- Закрываем курсор
CLOSE MyCursor1;
GO
-- Закрываем курсор
CLOSE MyCursor2;
GO

-- Уничтожаем курсор
DEALLOCATE MyCursor1;
GO
-- Уничтожаем курсор
DEALLOCATE MyCursor2;
GO 

В результате 2 ошибки:
1. Указывает на строчку "CREATE LOGIN MyCursor1 WITH PASSWORD = MyCursor2;", а точнее на курсор "MyCursor2" и пишет следующее "Неправильный синтаксис около "MyCursor2". Ожидается String, Text_LEX или BINARY."
2. Указывает на строчку "CLOSE MyCursor1;", а точнее на оператор "CLOSE" и пишет следующее "Неправильный синтаксис около "CLOSE". Ожидается CONVERSATION".

[Dimka]

RXL, так, наверно, даже лучше будет. А также триггеры на update и delete.

[zuze]

RXL, так, наверно, даже лучше будет. А также триггеры на update и delete.

А почему методом "триггер AFTER INSERT" будет лучше, чем методом Курсор?

[RXL]

Тем, что не надо делать курсор. И тем, что интерфейс упрощается до табличного CRUD.

[Dimka]

RXL, не только. Я в первую очередь подумал о "когерентности" - т.е. соответствии реальных логинов содержимому таблицы.

[zuze]

А может на vxod повесить триггер AFTER INSERT и вызывать CREATE USER из него?

Попробовал сделать:

Код:

USE master;
GO
-- Создание БД
CREATE DATABASE test;
GO

-- Переход в БД
USE test;
GO
-- Создание таблицы
CREATE TABLE vxod (id INTEGER NOT NULL PRIMARY KEY IDENTITY, my_name CHAR(10) NOT NULL, my_address CHAR(20) NOT NULL, my_password CHAR(3) NOT NULL);
GO

-- Заполнение таблицы
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Ira', 'Forumskaya', '111');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Petya', 'Chatovskaya', '222');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Yana', 'Zubovskaya', '333');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Maksim', 'Ryabovo', '444');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Roman', 'Romovskaya', '555');
GO

-- Создание триггера
CREATE TRIGGER	MyTrigger
 -- Закрепление триггера за определённой таблицей
ON vxod
AFREAR INSERT -- Указание вида триггера
AS
	SELECT my_name, my_password FROM vxod WHERE id <= 5;
	CREATE LOGIN my_name WITH PASSWORD = my_password WHERE id <= 5;
	CREATE USER my_name FOR LOGIN [my_name] WHERE id <= 5;
	EXEC sp_addsrvrolemember my_name, 'sysadmin' WHERE id <= 5;
GO

Возникают ошибки:
1. Указывает на строчку "CREATE LOGIN my_name WITH PASSWORD = my_password WHERE id <= 5;", а точнее на имя столбца таблицы "my_password" и пишет следующее "Неправильный синтаксис около "my_password". Ожидается String, Text_LEX или BINARY."
2. Указывает на строчку "CREATE USER my_name FOR LOGIN [my_name] WHERE id <= 5;" , а точнее на оператор "WHERE" и пишет следующее "Неправильный синтаксис около "WHERE"".
3. Указывает на строчку "EXEC sp_addsrvrolemember my_name, 'sysadmin' WHERE id <= 5;" , а точнее на оператор "WHERE" и пишет следующее "Неправильный синтаксис около "WHERE"".

Как избавится от этих ошибок?

[RXL]

Каков формат идентификаторов переменных? Если не ошибаюсь, MS использует префикс "@".

[zuze]

Каков формат идентификаторов переменных? Если не ошибаюсь, MS использует префикс "@".

У меня же не переменные, а имена столбцов таблицы. К ним вроде не нужно обращаться как к переменным.

[RXL]

zuze, я не знаю особенности TSQL, то скажу точно, что ты путаешь административные команды и DML.

[Dimka]

RXL, да, именно так - нужно из таблицы выгрузить в локальные переменые, и только потом подставлять значения в конструкции административных команд.

[zuze]

zuze, я не знаю особенности TSQL, то скажу точно, что ты путаешь административные команды и DML.

RXL, да, именно так - нужно из таблицы выгрузить в локальные переменые, и только потом подставлять значения в конструкции административных команд.

Я попытался сделать локальные переменные:

Код:

USE master;
GO
-- Создание БД
CREATE DATABASE test;
GO

-- Переход в БД
USE test;
GO
-- Создание таблицы
CREATE TABLE vxod (id INTEGER NOT NULL PRIMARY KEY IDENTITY, my_name CHAR(10) NOT NULL, my_address CHAR(20) NOT NULL, my_password CHAR(3) NOT NULL);
GO

-- Заполнение таблицы
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Ira', 'Forumskaya', '111');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Petya', 'Chatovskaya', '222');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Yana', 'Zubovskaya', '333');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Maksim', 'Ryabovo', '444');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Roman', 'Romovskaya', '555');
GO

DECLARE @i INT;
DECLARE @name CHAR(10);
DECLARE @password CHAR(3);
SET @i = 1;
WHILE @i <= 5
BEGIN
 SET @name = 'SELECT my_name FROM vxod WHERE id = @i';
 SET @password = 'SELECT my_name FROM vxod WHERE id = @i';
 CREATE LOGIN @name WITH PASSWORD = @password;
 CREATE USER @name FOR LOGIN [@name];
 EXEC sp_addsrvrolemember @name, 'sysadmin';
 SET @i = @i + 1;
END;
GO

Возникают ошибки:
1. Указывает на строчку "CREATE LOGIN @name WITH PASSWORD = @password;", а точнее на переменную "@name" и пишет следующее "Неправильный синтаксис около "@name". Ожидается ID или QUOTED_ID."
2. Указывает на строчку "CREATE LOGIN @name WITH PASSWORD = @password;" , а точнее на знак "=" и пишет следующее "Неправильный синтаксис около "=". Ожидается '(' или AS.".
3. Указывает на строчку "CREATE USER @name FOR LOGIN [@name];" , а точнее на переменную "@name" и пишет следующее "Неправильный синтаксис около "@name". Ожидается ID или QUOTED_ID.".

Как избавится от этих ошибок?

[Dimka]

zuze, подсказка: а что означают квадратные скобки по-твоему?

[zuze]

zuze, подсказка: а что означают квадратные скобки по-твоему?

Большое спасибо! Как я понял квадратные скобки нужны для того, что бы экранировать элементы SQL-запроса, если эти элементы не подходят под стандартные требования.

Переделал так:

Код:

USE master;
GO
-- Создание БД
CREATE DATABASE test;
GO

-- Переход в БД
USE test;
GO
-- Создание таблицы
CREATE TABLE vxod (id INTEGER NOT NULL PRIMARY KEY IDENTITY, my_name CHAR(10) NOT NULL, my_address CHAR(20) NOT NULL, my_password CHAR(3) NOT NULL);
GO

-- Заполнение таблицы
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Ira', 'Forumskaya', '111');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Petya', 'Chatovskaya', '222');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Yana', 'Zubovskaya', '333');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Maksim', 'Ryabovo', '444');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Roman', 'Romovskaya', '555');
GO

DECLARE @i INT;
DECLARE @name CHAR(10);
DECLARE @password CHAR(3);
SET @i = 1;
WHILE @i <= 5
BEGIN
 SET @name = 'SELECT my_name FROM vxod WHERE id = @i';
 SET @password = 'SELECT my_name FROM vxod WHERE id = @i';
 CREATE LOGIN [@name] WITH PASSWORD = [@password];
 CREATE USER [@name] FOR LOGIN [@name];
 EXEC sp_addsrvrolemember [@name], 'sysadmin';
 SET @i = @i + 1;
END;
GO

Возникает ошибка:
Указывает на строчку " CREATE LOGIN [@name] WITH PASSWORD = [@password];", а точнее на переменную "@password" и пишет следующее "Неправильный синтаксис около "@name". Ожидается String, Text_LEX или BINARY."

Как я понял это ошибка возникает из-за того, что я у переменной "@password" сделал тип "CHAR(3)", но я это сделал потому, что в таблице такой же тип, а в запросе должен быть тип "String, Text_LEX или BINARY".
Наверно надо как-то преобразовать или объявить иначе, а может что-то иное сделать с переменной.

Помогите пожалуйста разобраться.

[Dimka]

zuze, увы, ты не разбираешься, а лепишь всё подряд - в этом и проблема. Какова область применения квадратных скобок? К чему они приложимы, а к чему нет? В частности @name подходит под "стандартные требования", как ты выражаешься, или не подходит? Если да, то почему? Если нет, то почему?

[zuze]

Какова область применения квадратных скобок?

Использование в SQL операторах.

К чему они приложимы, а к чему нет?

Можно использовать к именам таблиц, столбцов, переменных.

В частности @name подходит под "стандартные требования", как ты выражаешься, или не подходит?

Прошу прощения за "стандартные требования". Как-то не научно выразился. "@name" - не подходит под "стандартные требования".

Если нет, то почему?

Так как "name" является зарезервированным именем. И что бы переменная "@name" воспринималась как переменная, а не как зарезервированное имя, делаем её в квадратных скобках.

[Dimka]

Ты вообще описание команды читал?

http://msdn.microsoft.com/en-us/library/ms173463(v=sql.120).aspx

Пароль задаётся значением, а логин - идентификатором. Значит это только динамическим запросом делается.

Раньше в старых версиях SQL Server была sp_adduser - там можно было значениями задавать.

[zuze]

Ты вообще описание команды читал?

http://msdn.microsoft.com/en-us/library/ms173463(v=sql.120).aspx

Пароль задаётся значением, а логин - идентификатором. Значит это только динамическим запросом делается.

Наверно Вы имели ввиду не команду "CREATE USER", а команду "CREATE LOGIN", так как там указывается пароль.

А как же тогда взять значения пароля из таблицы, если нельзя указывать переменную?

[RXL]

Считываешь значения в переменные: через курсор или через SELECT INTO. Потом составляешь строку запроса и подставляешь туда переменные. Выполняешь составленный динамический запрос.

[zuze]

Считываешь значения в переменные: через курсор или через SELECT INTO. Потом составляешь строку запроса и подставляешь туда переменные. Выполняешь составленный динамический запрос.

Я объявил значение

Код:

DECLARE @password CHAR(3);

В цикле сделал

Код:

SET @password = 'SELECT my_password FROM vxod WHERE id = @i';

В цикле сделал запрос

Код:

CREATE LOGIN [@name] WITH PASSWORD = [@password];

Возникает ошибка:
Указывает на строчку " CREATE LOGIN [@name] WITH PASSWORD = [@password];", а точнее на переменную "@password" и пишет следующее "Неправильный синтаксис около "@password". Ожидается String, Text_LEX или BINARY."

Вроде делаю, как Вы сказали, но возникает ошибка. Как же правильно нужно было?

[Dimka]

Тяжёлый случай. Причём клинический.

[Sla]

ррррр.... гав...

[zuze]

Считываешь значения в переменные: через курсор или через SELECT INTO. Потом составляешь строку запроса и подставляешь туда переменные. Выполняешь составленный динамический запрос.

Тяжёлый случай. Причём клинический.

ррррр.... гав...

Прошу прощения за долгое понимание, мне кажется я понял о чём Вы говорите. Надо использовать "CONCAT()" для сборки динамического запроса, а затем его запустить с помощью "PREPARE()" или "EXECUTE()".

[Dimka]

zuze, чего тебе надо, так это разобраться с самим предметом программирования вообще. Но судя по предыдущим темам, прогресса никакого. Ты просто методом тыка ищешь правильную комбинацию шаманских значков без малейшего понимания, как последовательно сконструировать правильное выражение. Вот в школьной математике есть задачки "упростить выражение", а тут наоборот: взять из справочника конструкцию, и если какие-то её части не определены или не работают, доопределить и расширить, сохраняя синтаксическую правильность выражения. Ты делаешь не так. Если не работает, ты выкидываешь всё и генерируешь вариант разной степени бредовости с нуля. Как правило, он тоже не работает. В итоге происходит барахтанье на одном месте без всякого прогресса. Причём это место совершенно ровное. Т.е. для посторонних остаётся два варианта: или дать тебе сразу готовое решение, или вообще не вмешиваться, потому что, укажи тебе направление, ты его просто не придерживаешься, немедленно теряешь ориентацию, не можешь сконцентрироваться. Если так, тут медицина бессильна.

[zuze]

Задача решена. Всем огромное спасибо за помощь!

Если кому вдруг понадобится вот код:

Тут я создаю БД и заполняю таблицу

Код:

USE master;
GO
-- Создание БД
CREATE DATABASE test;
GO
-- Переход в БД
USE test;
GO

-- Создание таблицы
CREATE TABLE vxod (id INTEGER NOT NULL PRIMARY KEY IDENTITY, my_name CHAR(10) NOT NULL, my_address CHAR(20) NOT NULL, my_password CHAR(3) NOT NULL);
GO

-- Заполнение таблицы
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Ira', 'Forumskaya', '111');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Petya', 'Chatovskaya', '222');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Yana', 'Zubovskaya', '333');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Maksim', 'Ryabovo', '444');
GO
INSERT INTO vxod (my_name, my_address, my_password) VALUES ('Roman', 'Romovskaya', '555');
GO

Тут создаю пользователей используя данные из ранее заполненной таблицы

Код:

USE test;
GO

DECLARE @i INT;
DECLARE @name CHAR(10);
DECLARE @password CHAR(3);
DECLARE @sql AS NVARCHAR(805);

SET @i = 1;
WHILE @i <= 5
BEGIN
 SELECT @name = my_name FROM vxod WHERE id = @i;
 SELECT @name; 
 SELECT @password = my_password FROM vxod WHERE id = @i;
 SELECT @password;
 SET @sql = 'CREATE LOGIN [' + @name + '] WITH PASSWORD = ''' + @password + '''';
 EXEC(@sql);
 SET @sql = 'CREATE USER [' + @name + '] FOR LOGIN [' + @name + ']';
 EXEC(@sql);
 SET @sql = 'sp_addsrvrolemember [' + @name + ']' + ',' + 'sysadmin';
 EXEC(@sql);
 SET @i = @i + 1;
END;
GO

[Алексей++]

>>Задача решена.

внезапно

[Dimka]

Алексей++, меня тоже терзают смутные сомнения.

[zuze]

>>Задача решена.

внезапно

Алексей++, меня тоже терзают смутные сомнения.

Я учёл все Ваши замечания и подсказки, посмотрел как запросы оформляются именно для ms sql и у меня всё получилось.
Ещё раз всем большое спасибо!