VietNetwork.Vn

Khi bạn restore database sang một server khác, một tình huống thường xảy ra là một user vốn đang có thể truy nhập vào database ở server cũ nay không thể truy nhập vào database đó trên server mới, mặc dù bạn vẫn nhìn thấy user này trong database mới được restore. Tình huống này xảy ra vì mỗi user của database luôn được gắn với một login ở mức server, thông qua Security ID. Khi database được restore sang server mới, trên server mới không có SQL login nào giống với server cũ (cùng Security ID) để user đó gắn vào, nó đã bị "lạc mẹ" (orphaned user).

Để minh họa, giả sử tôi đã có sẵn database trên server_1, tôi sẽ tạo một login trên server_1 và thêm user vào database đó, rồi thực hiện backup database. Trên server_2 tôi cũng tạo một login cùng tên, rồi restore database vừa được backup lên server_2. Sau đó ta sẽ quan sát chuyện gì xảy ra. Code để thực hiện các bước trên:

Server_1

-- Trên server_1 giả sử tôi đã có database TestDB
-- Tạo login tên là spiderman

CREATE LOGIN spiderman WITH PASSWORD= N'1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

-- Trong TestDB tạo user từ login này và gán quyền đọc
USE TestDB
GO
CREATE USER spiderman FROM LOGIN spiderman WITH default_schema=dbo
EXEC SP_ADDROLEMEMBER N'db_datareader', N'spiderman'

-- Backup database
BACKUP DATABASE TestDB TO  DISK = N'G:\Backup\TestDB.bak' WITH INIT

Server_2

-- Tạo login tên là spiderman
CREATE LOGIN spiderman WITH PASSWORD= N'1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

-- Restore database
RESTORE DATABASE TestDB
FROM DISK='\\server_1\g$\backup\TestDB.bak' --thay bằng tên server thực
WITH move 'TestDB' TO 'D:\DATA\TestDB.mdf', --thay bằng ổ đĩa thực
MOVE 'TestDB_log' TO 'L:\LOG\TestDB_log.ldf' --thay bằng ổ đĩa thực

Giờ nếu bạn cố gắng login vào server_2 bằng spiderman và truy nhập vào TestDB, bạn sẽ nhận được thông báo lỗi "The database TestDB is not accessible". Mặc dù vậy nếu bạn login bằng một login có quyền dbo trên TestDB, bạn vẫn nhìn thấy user spiderman trong database. Và nếu bạn tạo lại user:

--Server_2

USE TestDB
GO
CREATE USER spiderman FROM LOGIN spiderman WITH default_schema=dbo

Bạn sẽ nhận được lỗi "User, group, or role 'spiderman' already exists in the current database.". Vậy là bạn biết ngay user spiderman đã bị lạc mẹ rồi. Để tìm tất cả các user bị lạc mẹ trong TestDB, bạn chạy thủ tục sp_change_users_login như sau:

EXEC TestDB..SP_CHANGE_USERS_LOGIN 'Report'

Nếu muốn tìm hiểu sâu hơn, bạn có thể kiểm tra Security ID (SID) của login spiderman và user spiderman.
Trên server_1, bạn hãy chạy hai lệnh sau và để ý cột SID:

-- Lấy SID của login spiderman
SELECT * FROM syslogins
WHERE name = 'spiderman'

-- Lấy SID của user spiderman
SELECT *
FROM TestDB..sysusers
WHERE name = 'spiderman'

Bạn sẽ thấy SID từ hai lệnh là giống nhau, như trên máy của tôi SID = '0xB328F2B9ABBA3F41BD2B18D9B33ECF6B'.

Sau đó quay sang server_2 và chạy hai lệnh trên, SID từ hai lệnh giờ sẽ khác nhau. Trên máy của tôi:
SID của login spiderman = '0x901C681EC98CB942B2B19A74C7F9E915', tạo ra khi login spiderman được tạo.
SID của user spiderman = '0xB328F2B9ABBA3F41BD2B18D9B33ECF6B', SID này được copy từ server_1 sang.

Và thế là user spiderman không còn gắn với login spiderman nữa mặc dù cả hai vẫn cùng tồn tại và có cùng tên.
Để sửa user này bạn cũng dùng thủ tục sp_change_users_login:

USE TestDB
GO
EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE','spiderman','spiderman'

Bạn có thể kiểm tra để thấy user spiderman giờ có cùng SID với login spiderman. Lưu ý là tình huống này chỉ xảy ra đối với SQL login, còn với login được tạo từ một windows domain account thì Security ID được quản lý bởi windows hoặc active directory nên đều giống nhau trên các server.