SQL Server 中登入帳號與資料庫使用者移轉，sqlserver

SQL Server 中登入帳號與資料庫使用者移轉，sqlserver

1.      先建立一個SqlServer身分識別驗證的登入名稱，並映射到資料庫中。

如：建立使用者 [kk] 映射到資料庫 [mytest]，此時資料庫 [mytest]會增加一個使用者 [kk]

2.      此時再刪除登入名稱 [kk]，刪除後，資料庫[mytest]將存在一個孤立使用者 [kk]

3.      查看當前資料庫中是否存在孤立使用者

use mytest;exec sp_change_users_login @Action='Report';

4.      對於孤立使用者，有兩種情況：

a 不知道這些資料庫使用者之前的登入名稱或記不清楚資料庫來源

b 對於資料庫遷移一種情況，先遷移資料庫，再遷移登入帳號

 

第一種情況：

--建立登入名稱use master;create login [kk] with password = '123456';go--對孤立使用者串連到現有的登入名稱use mytest;exec sp_change_users_login @action='update_one', @usernamepattern='kk', --資料庫孤立使用者@loginname='kk'; --關聯到sql server登入名稱go--也可以再次修改密碼use master gosp_password @old=null, @new='654321', @loginame='kk';go

第二種情況：參考 如何在 SQL Server 2005 執行個體之間傳輸登入和密碼

 

如：要建立與另一個資料庫結構一樣的資料庫，可以匯出指令碼到另一台伺服器中執行（其中有資料庫使用者）

注意：資料庫使用者的許可權並沒有隨之授予，須手動再次授予許可權！

在原資料庫中建立預存程序：

USE masterGOIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL  DROP PROCEDURE sp_hexadecimalGOCREATE PROCEDURE sp_hexadecimal    @binvalue varbinary(256),    @hexvalue varchar (514) OUTPUTASDECLARE @charvalue varchar (514)DECLARE @i intDECLARE @length intDECLARE @hexstring char(16)SELECT @charvalue = '0x'SELECT @i = 1SELECT @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 + 1ENDSELECT @hexvalue = @charvalueGO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL  DROP PROCEDURE sp_help_revloginGOCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL ASDECLARE @name sysnameDECLARE @type varchar (1)DECLARE @hasaccess intDECLARE @denylogin intDECLARE @is_disabled intDECLARE @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_nameOPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denyloginIF (@@fetch_status = -1)BEGIN  PRINT 'No login(s) found.'  CLOSE login_curs  DEALLOCATE login_curs  RETURN -1ENDSET @tmpstr = '/* sp_help_revlogin script 'PRINT @tmpstrSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'PRINT @tmpstrPRINT ''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   ENDCLOSE login_cursDEALLOCATE login_cursRETURN 0GO

執行預存程序，將產生登入名稱的建立指令碼

EXEC sp_help_revlogin

將指令碼拷貝到當前資料庫孤立使用者的伺服器中執行（更多注意問題參考如何在 SQL Server 2005 執行個體之間傳輸登入和密碼）

CREATE LOGIN [kk] WITH PASSWORD = 0x0100FDBC7416947C56E903E945B5DF891643064BB7D16381577F HASHED, SID = 0xAE142AE3C75E9341B106B9BAA60BB0CC, DEFAULT_DATABASE = [mytest], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

雖然登入名稱和資料庫使用者已經存在了，但是原來資料庫使用者的角色身份、對象許可權都沒有了！

 

這時，在原資料庫執行以下指令碼，拷貝指令碼到新的資料庫中執行，將許可權還原

（授予資料庫孤立使用者權限或者其他所有使用者的許可權都“遷移”過來）

--更改【此使用者擁有的架構】select s.name,p.name,'ALTER AUTHORIZATION ON SCHEMA::['+s.name+'] TO ['+p.name+']'from sys.schemas s inner join sys.database_principals p on s.principal_id=p.principal_idwhere s.name <> p.name--授予【資料庫角色成員資格】許可權SELECT 'exec sp_addrolemember N'''+g.name+''', N'''+u.name+''''FROM sys.database_principals uinner join sys.database_role_members m on u.principal_id = m.member_principal_idinner join sys.database_principals g on g.principal_id = m.role_principal_idORDER BY g.name,u.name--授予【安全性實體】許可權SELECT N'grant '+B.permission_name  collate chinese_prc_ci_ai_ws+N' on ['+A.name+N'] to ['+C.name+N']'FROM sys.sysobjects A(NOLOCK) INNER JOIN sys.database_permissions B(NOLOCK) ON A.id=B.major_id INNER JOIN sys.database_principals C(NOLOCK) ON B.grantee_principal_id=C.principal_id--WHERE C.name='kk' --A.name='objectName'

如果【資料庫使用者】還有對其他對象有操作許可權，另外授予（當前不列出所有許可權）

至此，完成帳號遷移，步驟如下：

1 資料庫遷移，產生孤立使用者

2 登入帳號遷移，關聯孤立使用者

3 資料庫使用者權限遷移，所有操作許可權重新授予

 

 

參考：

如何在SQL Server 2005 執行個體之間傳輸登入和密碼

孤立使用者故障排除(SQLServer)