W przypadku przenoszenia lub kopiowania baz danych SQL na inny serwer można zaobserwować problem "osieroconych użytkowników". Artykuł ma na celu zaprezentowanie sposobów rozwiązania problemu „osieroconych użytkowników" w MS SQL Server 2000/2005/2008/2008 R2.
Z życia wzięte
Bardzo często musimy przenieść lub skopiować bazę danych na inny serwer. Szybko i ochoczo bierzemy się do pracy. Robimy Backup, Restore, zakładamy wymagane loginy, wpisujemy hasła itd., Uruchamiamy i... NIC!.
Werdykt: „osierociliśmy użytkowników".
SID czyli Security Identification Number
Microsoft SQL Server przechowuje informacje o loginach w bazie master w tabeli systemowej sysxlogins. Podczas tworzenia nowego loginu zostaje nadany unikalny identyfikator logowania. Jest to pole o nazwie SID (security identification number), typu varbinary (85).
Rysunek 1. Zapis SID w widoku syslogins.
W wersji SQL 2000 operuje się na tabeli sysxlogins, a od wersji SQL 2005 na widoku systemowym syslogins.
Jednocześnie w tabeli systemowej sysusers umieszczonej w każdej bazie danych, zapisane są informacje o użytkownikach, którzy mają dostęp do danej bazy. Patrz Rysunek2.
Na Rysunku 1. i Rysunku 2. zakreślono na czerwono różne SID-y (problemem „osieroconych użytkowników").
Rysunek 2. Zapis SID w tabeli sysusers.
Powiązanie tabel sysusers wybranej bazy danych z tabelą systemową lub widokiem sysxlogins/syslogins następuje za pomocą pola SID. Patrz Rysunek 3.
Rysunek 3. Mapowanie za pomocą SID-a pomiędzy Syslogins a Sysusers.
Problem osierocenia
Podczas przenoszenia baz danych z jednego MS SQL Servera na drugi MS SQL Server lub z jednej instancji na inną w ramach tego samego MS SQL Servera, może pojawić się niedopasowanie między unikalnymi identyfikatorami logowania (SID), zawartymi w tabelach syslogins i sysusers.
Problem ten był już częściowo poruszany w poradzie: „Jak zamienić sid usera z loginu w SQL Server 2000?": http://wss.pl/FaqList/1,18790/asc/Comments.aspx
Wynika on z dwóch faktów:
- 1. Podczas odtwarzania z kopii bazy danych, w tabeli sysusers zawarte są SID-y nadane na „starym" serwerze. Na nowym serwerze mamy loginy z innymi SID-ami.
- 2. Podczas odtwarzania z kopii bazy danych, w tabeli sysusers zawarte są SID-y nadane na „starym" serwerze. Na nowym serwerze nie mamy loginów, więc zakładamy je
z innymi SID-ami.
Problem ten nazywamy „osieroceniem użytkowników" (patrz: „Troubleshooting Orphaned Users" - http://support.microsoft.com/kb/q274188/pl), które wywołane jest błędnym SID-em lub brakiem loginu w bazie systemowej syslogins.
Został on dokładne opisany w „Kroku 3." artykułu z bazy wiedzy Microsoft Knowledge Base: 314546 (http://support.microsoft.com/kb/314546/) HOW TO: Move Databases Between Computers that are Running SQL Server
Cały problem sprowadza się do podmiany SID-a w tabeli wybranej bazy danych sysusers na SID-y z tabeli sysxlogins bazy master w ramach tej samej nazwy loginu i usera.
sp_change_user_login
Rozwiązanie kryje się w użyciu procedury składowanej sp_change_user_login.
Składnia:
sp_change_users_login [ @Action= ] 'action'
[ , [ @UserNamePattern= ] 'user' ]
[ , [ @LoginName= ] 'login' ]
[ , [ @Password= ] 'password' ]
[;]
Argumenty:
[@UserNamePattern= ] 'user' ] - nazwa użytkownika z aktualnej bazy danych, typ danych sysname, domyślna wartość Null.
[@LoginName= ] 'login' ] - - nazwa loginu SQL Server, typ danych sysname, domyślna wartość Null.
[@Password= ] 'password' ] - hasło do nowego loginu, zakładanego przy użyciu Auto_fix. Musi być silne.
[ @Action= ] 'action' - dostępne trzy wartości:
Auto_fix -używane podczas automatycznej naprawy mapowań. Jeżeli na serwerze istnieje login (tabela syslogins), którego SID jest niezgodny z SID-em usera aktualnej bazy danych (tabela sysusers), SID usera podmieniany jest z SID-em loginu. Drugi przypadek ma miejsce, gdy nie istnieje na serwerze dany login. Wtedy automatycznie jest on zakładany z podanym hasłem, a następnie mapowany z SID-em usera aktualnej bazy.
W tym przypadku w parametrach należy nazwę loginu podać jako NULL i obowiązkowo wprowadzić hasło.
Np.
USE AdventureWorks2008R2;
GO
EXEC sp_change_users_login 'Auto_Fix', 'NazwaUsera', NULL, 'B3r12x$098f6';
GO
(W bazie AdventureWorks2008R2 istnieje użytkownik 'NazwaUsera'.
Jeśli istnieje login o tej nazwie, następuje tylko mapowanie.
Jeśli taki login nie istnieje, jest on zakładany z podanym hasłem.)
Report - wyświetla nazwy i SID-y z aktualnej bazy danych tych użytkowników (tabela sysusers), którzy nie są zmapowani z loginami (tabela syslogins).
W tym przypadku nie wpisuje się loginu i hasła.
Np.
USE AdventureWorks2008R2;
GO
EXEC sp_change_users_login 'Report';
GO
Rysunek 4. Wynik działania procedury sp_change_users_login 'Report'.
Update_One - mapuje istniejącego użytkownika aktualnej bazy danych (tabela sysusers)
z nazwą loginu (tabela syslogins).
W tym przypadku podaje się nazwę użytkownika, nazwę loginu i nie wpisuje się hasła lub podaje się nazwę użytkownika, nazwę loginu i wartość NULL.
Użycie Update_One można podzielić na dwa scenariusze:
- 1. Istnieją nazwy użytkownika i loginu. Należy tylko wykonać mapowanie.
USE AdventureWorksR2; GO
EXEC sp_change_users_login 'Update_One', 'User1', 'User1';
GO
2. Mapowanie użytkownika User1 do loginu User2.
--Dodanie nowego loginu.
USE master
GO
EXEC sp_addlogin 'NewLogin'
GO
--Zmiana mapowania użytkownika User1 na nowo utworzony login 'NewLogin'.
USE AdventureWorksR2
GO
EXEC sp_change_users_login 'Update_One', 'User1', 'NewLogin'
Uwaga: Nie można używać jako nazwy loginu 'sa', a jako nazwy użytkownika 'dbo', 'guest' oraz 'INFORMATION_SCHEMA'.
W przypadku przenoszenia baz danych na inny serwer można zaobserwować najczęściej stosowane trzy kroki, mające na celu rozwiązanie problemu „osieroconych użytkowników":
1. Dodanie loginu do bazy.
EXEC sp_addlogin 'username', 'P@ssw0rd'
GO
2. Naprawienie mapowań bazy danych.
EXEC sp_change_users_login 'update_one','username','username'
GO
3. Dodanie użytkownika do roli db_owner.
EXEC sp_addrolemember 'db_owner', 'username'
GO
Pomocne linki:
Autor: Dariusz Brejnak (dbsoft)
(MCP, MCTS-SQL Server 2008 Database Development, Implementation and Maintenance).
Pracuje jako programista i administrator baz danych, należy do Polish SQL Server User Group (PLSSUG), w wolnym czasie prowadzi blog o tej tematyce oraz dzieli się swoją wiedzą wśród społeczności IT pisząc artykuły i porady. Informatyką zawodowo zajmuje się od 1991 roku. Pasjonat programowania, baz danych, hurtowni danych oraz ogólnie rozumianej tematyki BI.