大多數的資料庫移轉, 不管是透過移動資料庫檔案, 或是使用備份還原的方式移動資料庫, 甚至使用複製資料庫精靈來操作, 多半都會遇到一個狀況, 就是使用者反應無法連線到資料庫進行存取.
這是一個蠻讓人頭痛的問題, 很多DBA在遇到此問題時, 就只有一個一個使用者帳號去重建, 建立後發現使用者可以順利登入到SQL Server囉~~ 可是好像又進不到資料庫來操作, 或是原本指定的權限都不正確了......
在被詢問到這個問題時, Colin通常就是一番長篇大論~~~ 目的就是讓大家能更了解資料庫帳號管理的部份, 所以就來看一下一個帳號存取資料表時的簡易觀念:
1. 使用者帳號登入到SQL Server, 這是會由系統的Logins來進行檢查是否有登入的權限
2. 確認能夠登入後, 就會檢查此帳號對於哪些Database具有存取的權限
3. 進入到Database後, 再由Database的User帳號資料對應該帳號具有哪些資料表的權限
是的, 在進行資料庫移轉動作時, 上述的第2點和第3點中的權限設定, 會跟著資料庫一併移轉, 所以在資料庫下的安全性裡, 仍然可以看到使用者帳號. 然而系統的Logins是存在master裡, 多數的Migration是不建議直接移轉master, 特別是在不同的版本移轉時. 所以, Logins沒有此帳號, 自然就無法登入到資料庫中.
而先前提到的, 若是手動再次建立這些Logins後, 即可登入到SQL Server, 但存取資料庫時會發生權限不正確的狀況. 對的, 因為存在資料庫中的Users資料, 對應的Logins是依照SID來做為參照的. 此時當重新建立這些Logins時, SID即會重新產生, 自然就無法對應了.
此時, 最笨的方式, 就是在原有的資料庫上去檢查每個帳號的權限, 然後重建所有的設定, 也就是從Logins進行新增, 然後把資料庫的Users刪除, 重新建立關連, 然後指派權限. 如果...... 有上百個使用者咧?
好家在微軟是有留後步的, 在微軟官方文件 (KB) 中, 有提到如何在SQL Server資料庫伺服器之間移動Logins, 這可是能連密碼都一起搬過去, 不用找個半死 (死活是DBA的事), 或是重新設定 (沒查覺的AP就別想連了)
以下就是針對SQL Server 7, 2000和2005, 2008的參考文件.
如何在 SQL Server 的執行個體之間傳送登入和密碼 (7.0/2000)
http://support.microsoft.com/kb/246133/zh-tw
如何在 SQL Server 2005 和 SQL Server 2008 的執行個體之間傳送登入和密碼
http://support.microsoft.com/kb/918992
使用方式並不困難, 就把文件中的script貼到SSMA的查詢中, 然後執行就會自動產生二個store procedure在master資料庫中.
* sp_hexadecimal
* sp_help_revlogin
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL
AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin
FROM sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name )
WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin
FROM sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name )
WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login:' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE
BEGIN
-- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
執行sp_help_revlogin這個store procedure後, 就會產生一連串的登入帳號資訊, 其中包含了Windows Account與SQL Account. (注意, 這裡指的Windows Account是指在SQL Server中加入的Windows帳戶, 而不是將Windows系統的帳戶做出來!!) 以下就各列出一個例子好了~~ 大致就長這樣子.
/* sp_help_revlogin script
** Generated 03 11 2013 4:02PM on <SERVERNAME> */
-- Login:<DOMAIN>\<Win_Account>
CREATE LOGIN [<DOMAIN>\<Win_Account>] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
-- Login:colin
CREATE LOGIN [colin] WITH PASSWORD = 0x00<略> HASHED, SID = 0xFF<略>, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
好啦, 洋洋灑灑的把SQL Server中的Logins全都列出來了, 但..... 哪些要移轉呢? 是的, 有些DBA就這麼全貼, 然後F5按下去, 就看到一堆紅字...... 跑得過的就加, 跑不過的就不加囉~~~ 這是很多人和我說的 @.@ 事實上, 很多時候在做移轉工作時, 也是好好整頓系統中沒有在使用的帳號最佳時機. 有很多AP兼DB的, 最喜歡有事沒事開一個systemadmin在那裡, 然後用完也不刪.... 不然就是弄一堆帳號也不知道要幹嘛, 就放在那裡看的很爽....
這時回到老文件裡面的步驟 3：如何解決孤兒使用者的問題 (忘記的請看上一篇), 這裡指的是, 當我們資料庫先做了備份還原, 或是detach/attach的方式, 搬到了新的伺服器上後, 資料庫原有的帳戶連結仍然存在, 這在前面說過了. 但是在新的伺服器沒有這個帳戶 (應該說SID), 或是就算重建了也不能連結 (因為SID不同了), 就有一個語法可以來做這些狀況的檢查.
EXEC sp_change_users_login 'Report'
不過要辛苦點, 每個DB都要去做一下, 然後把有列出來的帳戶, 從先前產生出來的Login Script中挑出來, 然後到新的伺服器上執行. 當然有重複的就第一次執行就好了, 就算第二次去執行也是報一個已經存在的訊息, 所以在移轉時不要手動先把Login建起來唷!!
再來是一些小地方要注意的, 也是每次我們在進行操作, 後面那一堆眼睛盯著看, 還要東問一下西問一下的.... 背後靈, 最喜歡問的問題
1. 為什麼Windows的Logins不用script來建立? 答: 同Domain/Servername還好, 不同時本來在Windows系統中的就不是長這樣, 手動加吧!!
2. 為什麼有些沒有被sp_change_users_login列出的, 就不做了? 答: 那些要問你們的DBA, 只有他們知道那些帳號還要不要.....
3. 為什麼script產生出來的, 沒有sa的資訊? 答: 安裝時不就建立了....... @.@
大地方要注意的是..... 在測試好多好多次, 以前也給微軟RD Team的人發過信, 不過好像還是沒有解決的是SQL Server Role.... 不會帶過去 >.< 那就自己做囉~~~ 一組簡單的組字串方式就可以實現了.
SELECT 'EXEC sys.sp_addsrvrolemember @loginame = ' + loginname + ', @rolename = N''sysadmin'''
FROM sys.syslogins WHERE sysadmin = 1 AND isntname = 0 UNION
SELECT 'EXEC sys.sp_addsrvrolemember @loginame = ' + loginname + ', @rolename = N''securityadmin'''
FROM sys.syslogins WHERE securityadmin = 1 AND isntname = 0 UNION
SELECT 'EXEC sys.sp_addsrvrolemember @loginame = ' + loginname + ', @rolename = N''serveradmin'''
FROM sys.syslogins WHERE serveradmin = 1 AND isntname = 0 UNION
SELECT 'EXEC sys.sp_addsrvrolemember @loginame = ' + loginname + ', @rolename = N''setupadmin'''
FROM sys.syslogins WHERE setupadmin = 1 AND isntname = 0 UNION
SELECT 'EXEC sys.sp_addsrvrolemember @loginame = ' + loginname + ', @rolename = N''processadmin'''
FROM sys.syslogins WHERE processadmin = 1 AND isntname = 0 UNION
SELECT 'EXEC sys.sp_addsrvrolemember @loginame = ' + loginname + ', @rolename = N''diskadmin'''
FROM sys.syslogins WHERE diskadmin = 1 AND isntname = 0 UNION
SELECT 'EXEC sys.sp_addsrvrolemember @loginame = ' + loginname + ', @rolename = N''dbcreator'''
FROM sys.syslogins WHERE dbcreator = 1 AND isntname = 0 UNION
SELECT 'EXEC sys.sp_addsrvrolemember @loginame = ' + loginname + ', @rolename = N''bulkadmin'''
FROM sys.syslogins WHERE bulkadmin = 1 AND isntname = 0
留言列表
SQL Server (5)