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

Откуда:
Сообщений: 264
Добрый день, коллеги!
При миграции с SQL2000 на SQL2008, возник вот ещё какой, очень хитрый вопрос:
Необходимо руками в Qwery Analyzer иметь возможность править системные таблицы.
Например, скажем, удалять и добавлять пользователя, или алиас на него ...

Вот скрипт, который отрабатывал в SQL2000:
#############
use Test_DB
go
sp_configure ALLOW_UPDATE, 1
go
select * from sysusers
go
delete from sysusers where uid=5
#############

Вот ошибка, которую возвращает в SQL2008 этот же скрипт:
#############
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
#############

Коллеги, не покиньте, не оставьте без внимания ...
17 ноя 09, 16:27    [7940837]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
iljy
Member

Откуда:
Сообщений: 8711
missing.fox,

начиная с 2005 прямое редактирование системных таблиц запрещено. Что мешает использовать документированные средства, скажет ADD USER, CREATE LOGIN?
17 ноя 09, 16:31    [7940878]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
missing.fox
Member

Откуда:
Сообщений: 264
Так сложилось, что база с локальными пользователями ...
База огромна ...
Происходит дамп, и должен быть подьём тут же этой базы на др. сервак ...
На др. серваке база поднимается, пользаки вилнв, но ... СИД-ы их другие, от старого сервака ...
соответственно, ничего не работает ...
Раньше скриптом поднинмалась база, дёргались пользаки, потом добавлялись и всё ОК, а вот теперь ...
Как быть теперь не понятно, если теперь нельзя обращаться к системным таблицам ...
17 ноя 09, 16:46    [7941022]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
Glory
Member

Откуда:
Сообщений: 104760
Использовать документированную команду ALTER USER
17 ноя 09, 16:53    [7941088]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5499
Блог
Еще в 2000 для этого была процедура sp_change_users_login
17 ноя 09, 16:53    [7941089]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
или перейти на SSIS пакет (transfer logins task)
--------------------------------------------------------------
Дьявол кроется в деталях.
17 ноя 09, 16:55    [7941101]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
missing.fox
Так сложилось, что база с локальными пользователями ...
База огромна ...
Происходит дамп, и должен быть подьём тут же этой базы на др. сервак ...
На др. серваке база поднимается, пользаки вилнв, но ... СИД-ы их другие, от старого сервака ...
соответственно, ничего не работает ...
Раньше скриптом поднинмалась база, дёргались пользаки, потом добавлялись и всё ОК, а вот теперь ...
Как быть теперь не понятно, если теперь нельзя обращаться к системным таблицам ...


Может это пригодится: Снимок УЗ в SQL Server
17 ноя 09, 20:16    [7942030]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
missing.fox
Member

Откуда:
Сообщений: 264
use Test_DB
go
sp_configure ALLOW_UPDATE, 1
go
RECONFIGURE WITH OVERRIDE
go
select * from sysusers
go
delete from sysusers where uid=5

Господа, вот текст выше,
sp_configure ALLOW_UPDATE, 1
go
RECONFIGURE WITH OVERRIDE
go
этот кусок не исполняется, потому, как теперь нельзя ...

команды ADD USER, CREATE LOGIN, ALTER LOGIN не дают результат для sysusers where uid=5, так, как это системный пользователь, у него !!!uid=5!!! ...
Способы обращения SSIS и ADO, это всего лишь обращениек серверу, через которое он, сервер, выполняет заданные команды, если не прав, поправьте.
Было написано, что, начиная с 2005-го сервера, к системным таблицам руками лезть запрешено, но оно мне надо ...
Есть ли возможность через хранимые процедуры редактировать, добавлять алиасы, добавлять пользователей в системных областях? Причём в базе master не обязательно, достаточно в пользовательских бвзвх.
Наверняка что-то подобное должно существовать ...
18 ноя 09, 08:08    [7942827]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
missing.fox
Наверняка что-то подобное должно существовать ...

существует, но в вашем случае всё-таки не вижу нужды лезть в них...ищите обходные решения...ИМХО!!!
18 ноя 09, 08:46    [7942899]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
petsa
Member

Откуда:
Сообщений: 1708
Ну а вот так удалить не получается?
SELECT [name] FROM sys.sysusers WHERE [uid] = 5;--получаем username
DROP USER username;
18 ноя 09, 08:51    [7942913]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
то missing.fox
ну дак а чем вариант перевести всё на SSIS пакет не устраивает?
--------------------------------------------------------------
Дьявол кроется в деталях.
18 ноя 09, 09:34    [7943111]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
Glory
Member

Откуда:
Сообщений: 104760
missing.fox


команды ADD USER, CREATE LOGIN, ALTER LOGIN не дают результат для sysusers where uid=5, так, как это системный пользователь, у него !!!uid=5!!! ...

А зачем что-то делать с системным пользователем то ?? Не вы его добавляли - не вам его и удалять
18 ноя 09, 10:29    [7943450]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
missing.fox
Member

Откуда:
Сообщений: 264
Надо сделать на него алиас ...
в 2000 это было можно, в 2008 этого нельзя сделать, программный комплекс заточен постарому ...
19 ноя 09, 14:27    [7950974]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
Glory
Member

Откуда:
Сообщений: 104760
missing.fox
Надо сделать на него алиас ...
в 2000 это было можно, в 2008 этого нельзя сделать, программный комплекс заточен постарому ...

Ну так и надо было продолжать использовать sql2000
Или отказаться от недокументированных способов работы
19 ноя 09, 14:31    [7951026]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
Дедушка
то missing.fox
ну дак а чем вариант перевести всё на SSIS пакет не устраивает?
--------------------------------------------------------------
Дьявол кроется в деталях.

К сожалению, SSIS Transfer logins task не умеет переносить пароли.

2автор топика:

скрипт синхронизации:

SET NOCOUNT ON

CREATE TABLE #Logins
(
    loginId int IDENTITY(1, 1) NOT NULL,
    loginName nvarchar(128) NOT NULL,
    passwordHash varbinary(256) NULL,
    sid varbinary(85) NOT NULL
)

-- openquery is used so that loginproperty function runs on the remote server,
-- otherwise we get back null
INSERT INTO #Logins(loginName, passwordHash, sid)
SELECT *
FROM OPENQUERY([имя_линкованого_сервера], '
SELECT name, CONVERT(varbinary(256), LOGINPROPERTY(name, ''PasswordHash'')), sid
FROM master.sys.server_principals
WHERE
    type = ''S'' AND 
    name NOT IN (''sa'', ''guest'')
ORDER BY name')

DECLARE 
    @count int, @loginId int, @loginName nvarchar(128), 
    @passwordHashOld varbinary(256), @passwordHashNew varbinary(256), 
    @sid varbinary(85), @sql nvarchar(4000), @password varchar(514)

SELECT @loginId = 1, @count = COUNT(*)
FROM #Logins

WHILE @loginId <= @count
BEGIN
    SELECT @loginName = loginName, @passwordHashNew = passwordHash, @sid = sid
    FROM #Logins
    WHERE loginId = @loginId

    -- if the account doesnt exist, then we need to create it
    IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE name = @loginName)
    BEGIN
        EXEC master.dbo.sp_hexadecimal @passwordHashNew, @password OUTPUT

        SET @sql = 'CREATE LOGIN [' + @loginName + '] WITH PASSWORD = '
        SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL')) 
        SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF' 
        --PRINT @sql
        EXEC (@sql)

        --PRINT 'login created'
    END
    -- if the account does exist, then we need to drop/create to sync the password;
    -- can't alter as hashed isn't supported
    ELSE
    BEGIN
        SELECT @passwordHashOld = CONVERT(varbinary(256), LOGINPROPERTY(@loginName, 'PasswordHash'))

        -- only bother updating if the password has changed since the last sync
        IF @passwordHashOld <> @passwordHashNew
        BEGIN
            EXEC master.dbo.sp_hexadecimal @passwordHashOld, @password OUTPUT

            SET @sql = 'DROP LOGIN [' + @loginName + ']'
            --PRINT @sql
            EXEC (@sql)

            SET @sql = 'CREATE LOGIN [' + @loginName + '] WITH PASSWORD = '
            SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL'))
            SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF' 
            --PRINT @sql
            EXEC (@sql)

            --PRINT 'login "altered"'
        END
    END

    SET @loginId = @loginId + 1
END

DROP TABLE #Logins
19 ноя 09, 15:00    [7951328]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
Александр Волок (def1983)
Member

Откуда: Rotterdam
Сообщений: 4959
В добавок, скрипт переноса серверных ролей:

declare @login varchar(100);
declare @role varchar(50);
declare ct cursor for
select s.name login, smm.name role from  [имя_линкованного_сервера].master.sys.server_principals s
join  [имя_линкованного_сервера].master.sys.server_role_members sm on s.principal_id = sm.member_principal_id
join   [имя_линкованного_сервера].master.sys.server_principals smm on sm.role_principal_id = smm.principal_id
where s.name not in ('sa', 'NT AUTHORITY\SYSTEM')

open ct
fetch next from ct into @login, @role
while @@fetch_status = 0
begin

exec sp_addsrvrolemember  @loginame=  @login ,  @rolename =  @role 

fetch next from ct into @login, @role
end

close ct
deallocate ct
19 ноя 09, 15:03    [7951358]     Ответить | Цитировать Сообщить модератору
 Re: Редактирование системных таблиц в SQL2008  [new]
missing.fox
Member

Откуда:
Сообщений: 264
Спасибо, коллеги.
я написал скрипт по переносу пользователей, ваши скрипты легли в основу!
Ещё раз искренне признателет, трансфер реализован )))
Ура!!!
20 ноя 09, 13:50    [7956488]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить