Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Добавление пользователей в базу скриптом  [new]
Shamil_U
Member

Откуда:
Сообщений: 7
Ситуация: есть MS SQL Server 2008 и есть БД, которую регулярно нужно восстанавливать заново из присылаемых "сверху" бэкапов. Соответственно, каждый раз три десятка подключаемых пользователей необходимо прописывать заново. Как можно автоматизировать этот процесс, сиречь можно ли добавлять пользователей скриптом и запускать этот скрипт через агента?
11 ноя 14, 16:52    [16829661]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Glory
Member

Откуда:
Сообщений: 104751
Shamil_U
сиречь можно ли добавлять пользователей скриптом и запускать этот скрипт через агента?

Можно.Добаваляйте.Запускайте.
11 ноя 14, 16:58    [16829701]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Shamil_U
Member

Откуда:
Сообщений: 7
Спасибо за ответ, Glory. Однако, как лучше написать скрипт? Я, конечно, могу написать череду из "CREATE USER", "CREATE SCHEMA" и "ALTER USER", но не существует ли другого способа - составить список пользователей и добавить их циклом?
11 ноя 14, 17:14    [16829866]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Glory
Member

Откуда:
Сообщений: 104751
Shamil_U
Я, конечно, могу написать череду из "CREATE USER", "CREATE SCHEMA" и "ALTER USER", но не существует ли другого способа - составить список пользователей и добавить их циклом?

И где же хранится ваш "список пользователей" ?
11 ноя 14, 17:15    [16829879]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Shamil_U
Member

Откуда:
Сообщений: 7
Glory, я могу составить список хоть как - хоть в текстовом файле, хоть отдельной таблицей в другой базе, хоть открытым текстом в теле скрипта. Изначально учётные записи находятся в домене Active Directory, соответствующие им логины на SQL Server есть.
11 ноя 14, 17:20    [16829932]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Glory
Member

Откуда:
Сообщений: 104751
Shamil_U
я могу составить список хоть как - хоть в текстовом файле, хоть отдельной таблицей в другой базе, хоть открытым текстом в теле скрипта.

Ну так и в чем проблема то для каждой записи таблицы/запроса сгенерировать команды и выполнить их ?
11 ноя 14, 17:23    [16829951]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Shamil_U
Member

Откуда:
Сообщений: 7
Эм, проблема в том, что я с трудом представляю, как должен выглядеть такой скрипт. Нужна именно принципиальная часть - вытащить данные и подставить их в команды. Читал форумы, в том числе и буржуйские, но так и не понял.
11 ноя 14, 17:27    [16829999]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Glory
Member

Откуда:
Сообщений: 104751
Shamil_U
как должен выглядеть такой скрипт

Какой "такой" ?
Что конкретно у вас не получается ?
Вы не можете написать цикл по записям ?
Считать значения полей в переменные ?
Сформировать команду ?...
11 ноя 14, 17:30    [16830038]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Плин перед пперподнятие бекапа

Databasae->Task->Generate Script

К сообщению приложен файл. Размер - 69Kb
11 ноя 14, 17:35    [16830094]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Shamil_U
Member

Откуда:
Сообщений: 7
Glory, я нашёл в Интернете и допилил под себя скрипт, который превращает данные из списка во временную таблицу, затем проходит по ней циклом и составляет команды. Проблема в том, что он загадочным образом работает только в Management Studio, а через Агент выдаёт ошибку "такой пользователь уже есть в базе", хотя база до выполнения скрипта совершенно чистая. Поэтому я поначалу хотел узнать альтернативное решение задачи. Скрипт прилагается, не могли бы вы на него взглянуть и раскритиковать?
SET NOCOUNT ON;

DECLARE @UserString VARCHAR(8000)
DECLARE @DatabaseString VARCHAR(8000)
DECLARE @DefaultDatabase VARCHAR(255)
DECLARE @RolesString VARCHAR(8000)
DECLARE @delimiter CHAR(1)

SET @UserString = '[Login1],[Login2],[Login3],.. <и так тридцать раз>'
SET @DatabaseString = '[TESTDB]'
SET @DefaultDatabase = '[TESTDB]'
SET @RolesString = 'db_datareader,db_owner'
SET @delimiter = ','
BEGIN TRY
    DROP TABLE #Users
    DROP TABLE #Databases
    DROP TABLE #Roles
END TRY BEGIN CATCH END CATCH

    ;WITH Substr(num, firstchar, lastchar) AS (
      SELECT 1, 1, CHARINDEX(@delimiter, @UserString)
      UNION ALL
      SELECT num + 1, lastchar + 1, CHARINDEX(@delimiter, @UserString, lastchar + 1)
      FROM Substr
      WHERE lastchar > 0
    )
    SELECT
        num,
        UserName = SUBSTRING(@UserString, firstchar, CASE WHEN lastchar > 0 THEN lastchar-firstchar ELSE 8000 END)
    INTO #Users
    FROM Substr
       
    ;WITH Substr(num, firstchar, lastchar) AS (
      SELECT 1, 1, CHARINDEX(@delimiter, @DatabaseString)
      UNION ALL
      SELECT num + 1, lastchar + 1, CHARINDEX(@delimiter, @DatabaseString, lastchar + 1)
      FROM Substr
      WHERE lastchar > 0
    )
    SELECT
        num,
        DatabaseName = SUBSTRING(@DatabaseString, firstchar, CASE WHEN lastchar > 0 THEN lastchar-firstchar ELSE 8000 END)
    INTO #Databases
    FROM Substr
   
    ;WITH Substr(num, firstchar, lastchar) AS (
      SELECT 1, 1, CHARINDEX(@delimiter, @RolesString)
      UNION ALL
      SELECT num + 1, lastchar + 1, CHARINDEX(@delimiter, @RolesString, lastchar + 1)
      FROM Substr
      WHERE lastchar > 0
    )
    SELECT
        num,
        RoleName = SUBSTRING(@RolesString, firstchar, CASE WHEN lastchar > 0 THEN lastchar-firstchar ELSE 8000 END)
    INTO #Roles
    FROM Substr    

DECLARE @NumUsers INT
DECLARE @NumDBs INT
DECLARE @NumRoles INT
DECLARE @UserIter INT
DECLARE @DBIter INT
DECLARE @RoleIter INT
DECLARE @UserName VARCHAR(255)
DECLARE @RoleUserName VARCHAR(255)
DECLARE @DBName VARCHAR(255)
DECLARE @RoleName VARCHAR(255)
DECLARE @SQL VARCHAR(MAX)

SET @NumUsers   = (SELECT MAX(num) FROM #Users)
SET @NumDBs     = (SELECT MAX(num) FROM #Databases)
SET @NumRoles   = (SELECT MAX(num) FROM #Roles)
SET @UserIter   = 1
SET @SQL        = ''
 
 WHILE @UserIter <= @NumUsers
BEGIN
    SET @DBIter = 1
    SET @UserName = (SELECT UserName FROM #Users WHERE num = @UserIter)
    SET @RoleUserName = REPLACE(REPLACE(@UserName, '[', ''), ']', '')
    SET @UserIter = @UserIter + 1
    -- Add user to the databases
    WHILE @DBIter <= @NumDBs
    BEGIN
        SET @RoleIter = 1
        SET @DBName = (SELECT DatabaseName FROM #Databases WHERE num = @DBIter)
        SET @SQL = 'USE ' + @DBName + '; CREATE USER ' + @UserName + ' FOR LOGIN [DOMAIN\' + @RoleUserName + ']'
        PRINT (@SQL)
        EXEC (@SQL)
        SET @SQL = 'CREATE SCHEMA ' + @UserName
        PRINT (@SQL)
        EXEC (@SQL)
        SET @SQL = 'USE ' + @DBName + '; ALTER USER ' + @UserName + ' WITH DEFAULT_SCHEMA=' + @UserName
        PRINT (@SQL)
        EXEC (@SQL)
        SET @DBIter = @DBIter + 1
        WHILE @RoleIter <= @NumRoles
        BEGIN
            SET @RoleName = (SELECT RoleName FROM #Roles WHERE num = @RoleIter)
            -- Must remove brackets for addrolemember procedure
            SET @SQL = 'USE ' + @DBName + '; EXEC sp_addrolemember ''' + @RoleName + ''', ''' + @RoleUserName + ''''
            PRINT (@SQL)
            EXEC (@SQL)
            SET @RoleIter = @RoleIter + 1
        END
    END
END

BEGIN TRY
    DROP TABLE #Users
    DROP TABLE #Databases
    DROP TABLE #Roles
END TRY BEGIN CATCH END CATCH


Maxx, интересный способ, но он создаёт именно череду CREATE_USER и CREATE_SCHEMA, которая мне не нравится. К тому же, сгенеренный скрипт почему-то сначала прописывает пользователя с назначенной схемой и только потом создаёт схему.
11 ноя 14, 17:58    [16830279]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Glory
Member

Откуда:
Сообщений: 104751
Shamil_U
а через Агент выдаёт ошибку "такой пользователь уже есть в базе", хотя база до выполнения скрипта совершенно чистая.

Серверу незачем вас обманывать. Он программа, а не человек.
11 ноя 14, 18:01    [16830308]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Shamil_U
Member

Откуда:
Сообщений: 7
Glory, я тоже не понимаю, как подобное безобразие воспринимать. Восстанавливаю базу, в ней ни одного подключённого пользователя нет. Запускаю только что выложенный скрипт через SQL Agent - он вываливается на третьем-четвёртом пользователе с ошибкой "пользователь уже есть в базе". Восстанавливаю базу ещё раз, копирую скрипт из Агента в окно Management Studio - отрабатывает без проблем. Такое ощущение, что где-то в Агенте счётчик цикла сбивается, но чем его можно заменить?
11 ноя 14, 18:06    [16830351]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4974
Shamil_U,

А через Transfer Sql Server Objects в SSIS не хотите это сделать?
11 ноя 14, 18:24    [16830477]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9162
Shamil_U
Ситуация: есть MS SQL Server 2008 и есть БД, которую регулярно нужно восстанавливать заново из присылаемых "сверху" бэкапов. Соответственно, каждый раз три десятка подключаемых пользователей необходимо прописывать заново. Как можно автоматизировать этот процесс, сиречь можно ли добавлять пользователей скриптом и запускать этот скрипт через агента?


Проблему не понял - базы без пользователей, базы имеют пользователей, но SID отличается от SID логинов или что?
Вам присылают бэкапы с разных серверов, что ли?
11 ноя 14, 18:32    [16830519]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Glory
Member

Откуда:
Сообщений: 104751
Shamil_U
Такое ощущение, что где-то в Агенте счётчик цикла сбивается, но чем его можно заменить?

Такое ощущение, что вы просто не знаете на какой базе и какие команды выполняет скрипт и какая именно команда вызывает ошибку
11 ноя 14, 21:02    [16831282]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
Shamil_U
Glory, я тоже не понимаю, как подобное безобразие воспринимать. Восстанавливаю базу, в ней ни одного подключённого пользователя нет. Запускаю только что выложенный скрипт через SQL Agent - он вываливается на третьем-четвёртом пользователе с ошибкой "пользователь уже есть в базе". Восстанавливаю базу ещё раз, копирую скрипт из Агента в окно Management Studio - отрабатывает без проблем. Такое ощущение, что где-то в Агенте счётчик цикла сбивается, но чем его можно заменить?
Наиболее вероятно, что из агета и SSMS скрипт выполняется для разных баз.
11 ноя 14, 22:05    [16831502]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Shamil_U
Member

Откуда:
Сообщений: 7
a_voronin
Shamil_U,

А через Transfer Sql Server Objects в SSIS не хотите это сделать?


Спасибо за совет, на досуге поизучаю технологию.

Владислав Колосов
Проблему не понял - базы без пользователей, базы имеют пользователей, но SID отличается от SID логинов или что?
Вам присылают бэкапы с разных серверов, что ли?


Нам присылают бэкап с какого-то "левого" сервера, в базе прописан ровно один пользователь, и тот с того сервера.

Glory
Такое ощущение, что вы просто не знаете на какой базе и какие команды выполняет скрипт и какая именно команда вызывает ошибку


Не спорю, потому и прошу помощи, или хотя бы подсказки, в каком направлении копать.

alexeyvg
Наиболее вероятно, что из агета и SSMS скрипт выполняется для разных баз.


Проверил - в агенте изначально указывалась база master. Исправил на свою - и скрипт прошёл влёт. Спасибо вам за ответ, пойду вырублю в граните, что "использование USE <database> не отменяет проверки, на какой базе выполняется скрипт".

Всем спасибо за ответы, вопрос закрыт, ушёл внимательно перечитывать скрипты.
12 ноя 14, 08:51    [16832161]     Ответить | Цитировать Сообщить модератору
 Re: Добавление пользователей в базу скриптом  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9162
Shamil_U
Нам присылают бэкап с какого-то "левого" сервера, в базе прописан ровно один пользователь, и тот с того сервера.


В этом случае Вам необходимо заскриптовать требуемых пользователей, группы, схемы (если требуется) базы и накатывать этот скрипт после каждого восстановления. Скрипты можно создать через контекстное меню в SSMS.
12 ноя 14, 11:27    [16833052]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить