SQL Server のインスタンス間でログインとパスワードを転送する
この記事では、Windows で実行されている SQL Server の異なるインスタンス間で、ログインとパスワードを転送する方法について説明します。
元の製品バージョン: SQL Server
元の KB 番号: 918992、246133
はじめに
この記事では、Microsoft SQL Server の異なるインスタンス間で、ログインとパスワードを転送する方法について説明します。
注:
インスタンスは、同じサーバー上または異なるサーバー上にあり、それらのバージョンが異なる場合があります。
詳細情報
この記事では、サーバー A とサーバー B は別のサーバーです。
サーバー A 上のSQL Serverのインスタンスからサーバー B 上のSQL Serverのインスタンスにデータベースを移動した後、ユーザーはサーバー B 上のデータベースにログインできない可能性があります。さらに、ユーザーは次のエラー メッセージを受け取ることがあります。
ユーザー 'MyUser' はログインできませんでした。 (Microsoft SQL Server、エラー: 18456)
この問題は、ログインとパスワードをサーバー A 上の SQL Server のインスタンスからサーバー B 上のSQL Serverのインスタンスに転送しなかったために発生します。
注:
18456 のエラー メッセージは、他の理由によっても発生します。 これらの原因と考えられる解決策の詳細については、「 MSSQLSERVER_18456」を参照してください。
ログインを転送するには、状況に応じて、次のいずれかの方法を使用します。
方法 1: コピー先のコンピューター (サーバー B) SQL Serverパスワードをリセットします。
この問題を解決するには、SQL Server コンピューターでパスワードをリセットし、ログインをスクリプト化します。
注:
パスワードをリセットする際、パスワード ハッシュ アルゴリズムが使用されます。
方法 2: ソース サーバー (サーバー A) で生成されたスクリプトを使用して、ログインとパスワードを移行先サーバー (サーバー B) に転送します。
ログインとそのパスワードの転送に必要なスクリプトを生成するために、ストアド プロシージャを作成します。 これを行うには、SQL Server Management Studio (SSMS) またはその他のクライアント ツールを使用してサーバー A に接続し、次のスクリプトを実行します。
USE [master] GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE [dbo].[sp_hexadecimal] ( @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT ) AS BEGIN 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 END go IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE [dbo].[sp_help_revlogin] ( @login_name sysname = NULL ) AS BEGIN 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 @Prefix VARCHAR(255) DECLARE @defaultdb SYSNAME DECLARE @defaultlanguage SYSNAME DECLARE @tmpstrRole VARCHAR (1024) IF (@login_name IS NULL) BEGIN DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name 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' AND p.name not like '##%' ORDER BY p.name END ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name 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 ORDER BY p.name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 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 SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''') BEGIN' Print @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']' 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 + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']' 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 SET @Prefix = ' EXEC master.dbo.sp_addsrvrolemember @loginame=''' SET @tmpstrRole='' SELECT @tmpstrRole = @tmpstrRole + CASE WHEN sysadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin''' ELSE '' END + CASE WHEN securityadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin''' ELSE '' END + CASE WHEN serveradmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin''' ELSE '' END + CASE WHEN setupadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin''' ELSE '' END + CASE WHEN processadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin''' ELSE '' END + CASE WHEN diskadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin''' ELSE '' END + CASE WHEN dbcreator = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator''' ELSE '' END + CASE WHEN bulkadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin''' ELSE '' END FROM ( SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName], sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin FROM sys.syslogins WHERE ( sysadmin<>0 OR securityadmin<>0 OR serveradmin<>0 OR setupadmin <>0 OR processadmin <>0 OR diskadmin<>0 OR dbcreator<>0 OR bulkadmin<>0 ) AND name=@name ) L PRINT @tmpstr PRINT @tmpstrRole PRINT 'END' END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage END CLOSE login_curs DEALLOCATE login_curs RETURN 0 END
注:
このスクリプトは、マスター データベースに 2 つのストアド プロシージャを作成します。 プロシージャには sp_hexadecimal と sp_help_revlogin という名前が付 けられます。
SSMS クエリ エディターで、[結果からテキストへ] オプションを選択します。
同じクエリ ウィンドウまたは新しいクエリ ウィンドウで次のステートメントを実行します。
EXEC sp_help_revlogin
sp_help_revlogin
ストアド プロシージャで生成される出力スクリプトは、ログイン スクリプトです。 このログイン スクリプトは、元のセキュリティ識別子 (SID) と元のパスワードでのログインを行います。
重要
移行先サーバーでの手順の実装に進む前に、次の 備考 セクションの情報を確認してください。
転送先サーバー (サーバー B) での手順
任意のクライアント ツール (SSMS など) を使用してサーバー B に接続し、手順 4 で生成されたスクリプト (の sp_helprevlogin
出力) をサーバー A から実行します。
注釈
サーバー B 上のインスタンスで出力スクリプトを実行する前に、次の情報を確認します。
パスワードは、次の方法でハッシュされている可能性があります。
-
VERSION_SHA1
: このハッシュは SHA1 アルゴリズムを使用して生成され、SQL Server 2000 から SQL Server 2008 R2 で使用されます。 -
VERSION_SHA2
: このハッシュは SHA2 512 アルゴリズムを使用して生成され、SQL Server 2012 以降のバージョンで使用されます。
-
出力スクリプトを注意深く確認してください。 サーバー A とサーバー B が異なるドメインにある場合は、出力スクリプトを変更する必要があります。 次に、ステートメントで新しいドメイン名を使用して、元のドメイン名を
CREATE LOGIN
置き換える必要があります。 新しいドメインでアクセス権が付与された統合ログインには、元のドメインのログインと同じ SID がありません。 そのため、ユーザーはこれらのログインを行えずに孤立します。 孤立したユーザーを解決する方法の詳細については、「孤立したユーザー (SQL Server) と ALTER USER のトラブルシューティング」を参照してください。
サーバー A とサーバー B が同じドメイン内にある場合は、同じ SID が使用されます。 そのため、ユーザーが孤立する可能性は低いです。出力スクリプトでは、暗号化されたパスワードを使用してログインが作成されます。 これは、
CREATE LOGIN
ステートメント内の HASHED 引数に基づいています。 この引数は、PASSWORD 引数の後に入力されたパスワードが既にハッシュされていることを指定します。既定では、sysadmin 固定サーバー ロールのメンバーのみが
sys.server_principals
ビューからSELECT
ステートメントを実行できます。 sysadmin 固定サーバー ロールのメンバーがユーザーに必要なアクセス許可を付与しない限り、ユーザーは出力スクリプトを作成または実行できません。この記事の手順では、特定のログインの既定のデータベース情報は転送されません。 これは、既定のデータベースがサーバー B に常に存在するとは限らないためです。ログインの既定のデータベースを定義するには、 ステートメントを
ALTER LOGIN
使用して、ログイン名と既定のデータベースを引数として渡します。ソース サーバーと宛先サーバーの並べ替え順序:
大文字と小文字を区別しないサーバー A と大文字と小文字を区別するサーバー B: サーバー A の並べ替え順序では大文字と小文字が区別されず、サーバー B の並べ替え順序では大文字と小文字が区別される場合があります。 この場合、ユーザーは、ログインとパスワードをサーバー B のインスタンスに転送した後、すべて大文字でパスワードを入力する必要があります。
大文字と小文字を区別するサーバー A と大文字と小文字を区別しないサーバー B: サーバー A の並べ替え順序は大文字と小文字が区別され、サーバー B の並べ替え順序では大文字と小文字が区別される場合があります。 この場合、次のいずれかの条件が当てはまる場合を除き、ユーザーはログインとサーバー B 上のインスタンスに転送するパスワードを使用してログインできません。
- 元のパスワードには文字が含まれていない。
- 元のパスワードのすべての文字は大文字です。
両方のサーバーで大文字と小文字を区別するか、大文字と小文字を区別しない: サーバー A とサーバー B の両方の並べ替え順序で大文字と小文字が区別される場合や、サーバー A とサーバー B の両方の並べ替え順序で大文字と小文字が区別される場合があります。 このような場合、ユーザーに問題は発生しません。
サーバー B 上のインスタンスに既に存在するログインには、出力スクリプトの名前と同じ名前が含まれている場合があります。 この場合、サーバー B 上のインスタンスで出力スクリプトを実行すると、次のエラー メッセージが表示されます。
メッセージ 15025、レベル 16、状態 1、行 1
サーバー プリンシパル 'MyLogin' は既に存在します。同様に、サーバー B 上のインスタンスに既に存在するログインには、出力スクリプトの SID と同じ SID がある場合があります。 この場合、サーバー B 上のインスタンスで出力スクリプトを実行すると、次のエラー メッセージが表示されます。
Msg 15433、レベル 16、状態 1、行 1 指定されたパラメータ sid は使用中です。
この場合は、次の作業を実行する必要があります。
出力スクリプトを注意深く確認してください。
サーバー B 上の
sys.server_principals
インスタンスのビューの内容を調べます。必要に応じて、これらのエラー メッセージに対処します。
SQL Server 2005 では、ログインの SID を使用してデータベース レベルのアクセスを実装します。 ログインは、サーバー上の異なるデータベースで異なる SID を持つ場合があります。 この場合、ログインはビュー内
sys.server_principals
の SID と一致する SID を持つデータベースにのみアクセスできます。 この問題は、2 つのデータベースが異なるサーバーから結合されている場合に発生する可能性があります。 この問題を解決するには、DROP USER ステートメントを使用して SID が一致しないデータベースからのログインを手動で削除します。 次に、ステートメントを使用してCREATE USER
ログインをもう一度追加します。