sql server中的孤立用户

 　　此问题出现在数据库的移值上。移值后，数据库的登陆名和数据库用户名孤立，原数据中，用建立的用户名密码登陆可以访问数据库，但是移值后就不能访问了。而且如果您尝试向该登录帐户授予数据库访问权限，则会因该用户已经存在而出现以下错误信息：     ‘该登录已经在另一个用户名下拥有帐户’。

   产生错误的原因是：
            在您向目标服务器传输登录帐户和密码后，您的用户可能还无法访问数据库。登录帐户与用户是靠安全识别符   (SID)   关联在一起的；在您移动数据库后，如果   SID   不一致，SQL   Server   可能会拒绝用户访问数据库。此问题称为孤立用户。如果您使用  SQL   Server   2000   DTS   传输登录功能来传输登录帐户和密码，就可能会产生孤立用户。此外，被允许访问与源服务器处于不同域中的目标服务器的集成登录帐户，也会导致出现孤立用户。 

 

 

场景1：无法登陆原实例，如果多个用户出现孤立情况

场景2：可以登录原实例

 

【0】 使用T-SQL获取登录名及密码

SELECT  'CREATE LOGIN [' + p.name + '] '
       + CASE WHEN p.type IN ( 'U', 'G' ) THEN 'FROM windows '
              ELSE ''
         END + 'WITH ' + CASE WHEN p.type = 'S'
                              THEN 'password = '
                                   + master.sys.fn_varbintohexstr(l.password_hash)
                                   + ' hashed, ' + 'sid = '
                                   + master.sys.fn_varbintohexstr(l.sid)
                                   + ', check_expiration = '
                                   + CASE WHEN l.is_expiration_checked > 0
                                          THEN 'ON, '
                                          ELSE 'OFF, '
                                     END + 'check_policy = '
                                   + CASE WHEN l.is_policy_checked > 0
                                          THEN 'ON, '
                                          ELSE 'OFF, '
                                     END
                                   + CASE WHEN l.credential_id > 0
                                          THEN 'credential = ' + c.name
                                               + ', '
                                          ELSE ''
                                     END
                              ELSE ''
                         END + 'default_database = '
       + p.default_database_name
       + CASE WHEN LEN(p.default_language_name) > 0
              THEN ', default_language = ' + p.default_language_name
              ELSE ''
         END
FROM    sys.server_principals p
       LEFT JOIN sys.sql_logins l
       ON p.principal_id = l.principal_id
       LEFT JOIN sys.credentials c
       ON l.credential_id = c.credential_id
WHERE   p.type IN ( 'S', 'U', 'G' )
       --AND p.name NOT IN ( 'sa')
               AND p.name NOT LIKE '%##%'
               AND p.name NOT LIKE '%NT SERVICE%'
               AND p.name NOT LIKE '%NT AUTHORITY%'

 

【1】查看解决孤立用户 

以下是解决办法：
 在目标服务器上打开查询分析器，然后在您移动的用户数据库中运行以下代码： 

【1】查找当前库的孤立用户：

use db_name; 
exec   sp_change_users_login   'REPORT';

【2】新建与孤立用户同名登录名后，进行新登录名与孤立用户的更新联合

Use  db_name;
sp_change_users_login 'update_one' ,  'user_name' ,  'login_name'

--查看当前数据库中是否存在孤立用户（只有用户，没有对应的登录名） 
exec sp_change_users_login @action='Report' 

-- 对孤立用户连接到现有的登录名 
use mytest;
 exec sp_change_users_login @action='update_one',
 @usernamepattern='kk', --数据库孤立用户 
 @loginname='kk'; --关联到sql server登录名 go 

--也可以使用2005之后的新语法替代SP_CHANGE_USERS_LOGIN ALTER USER MarketUser WITH Login = MarketUser

一般问题就会解决。
举例：
sp_change_users_login 'update_one' ,  'test' ,  'test'

【3】可以用登录名登录，但无法访问数据库

如果一个用户是孤立用户，数据库用户可以成功登录到服务器，但却无权访问数据库。如果您尝试向该登录帐户授予数据库访问权限，则会因该用户已经存在而出现以下错误信息：     

Microsoft   SQL - DMO   (ODBC   SQLState: 42000 )   Error    15023 : User     or    role    ' %s '    already    exists     in    the    current     database .

或是登陆名对应该的用户改成了dbo，则在sa下执行一下以下代码：

exec    sp_changedbowner    ' sa '  

然后再执行：

Use  db_name;
sp_change_users_login  ' update_one ' ,  ' user_name' ,  'login_name '

一般问题就会解决了。

 

场景1：无法登陆原实例，如果多个用户出现孤立情况

--如果存在同名的登录用户就绑定，不存在就创建（创建时候密码为空）并绑定
USE [GPOSDB] --要解决孤立用户的数据库名
GO
CREATE PROCEDURE [dbo].[sp_fix_orphaned_users]
AS
    BEGIN
        DECLARE @UserName NVARCHAR(64)
 
        CREATE TABLE #SqlLoginUser
        (
            UserName SYSNAME ,
            UserId INT IDENTITY(1, 1)
        )
         
        INSERT  INTO #SqlLoginUser( UserName ) SELECT  [name]  FROM  SYS.[sql_logins]
 
 
        CREATE TABLE #OrphanedUser
            (
              UserName SYSNAME ,
              UserId INT
            )
 
        INSERT  INTO #OrphanedUser EXEC sp_change_users_login @Action = 'Report';
 
        DECLARE Cur_OrphanedUsers CURSOR
        FOR
            SELECT  UserName
            FROM    #OrphanedUser;
        OPEN Cur_OrphanedUsers;
        FETCH NEXT FROM Cur_OrphanedUsers INTO @UserName;
        WHILE ( @@FETCH_STATUS = 0 )
            BEGIN
                IF ( @UserName IN ( SELECT  [UserName]
                                    FROM    [#SqlLoginUser] ) )
                    BEGIN
                        EXEC sp_change_users_login @Action = 'update_one',
                            @UserNamePattern = @UserName,
                            @LoginName = @UserName;
                    END
                ELSE
                    BEGIN
                        DECLARE @SQL NVARCHAR(200)
                        SET @SQL = 'CREATE LOGIN ' + @UserName + ' WITH PASSWORD='''''
                        EXEC(@SQL)
                        EXEC sp_change_users_login @Action = 'update_one',
                            @UserNamePattern = @UserName,
                            @LoginName = @UserName;
                    END
   
                FETCH NEXT FROM Cur_OrphanedUsers INTO @UserName
            END
        CLOSE Cur_OrphanedUsers
        DEALLOCATE Cur_OrphanedUsers
     
        DROP TABLE   #OrphanedUser
        DROP TABLE  #SqlLoginUser
    END
 
EXEC sp_fix_orphaned_users

 

场景2：可以登录原实例（使用系统SP或使用【0】中SQL）

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

exec sp_help_revlogin
-- 然后复制出来运行的结果，到迁移的机器上运行即可

结果如图：
　　

 

 

 

 

 

 

 

 

 

 

单个玩家详情片，镜像后详情篇

--=========================================
--在镜像搭建后,在主库服务器上创建登录,并在数据库上建立对应用户,
--数据库中用户被同步到镜像数据库中,但登录是实例级对象,无法同步,
--因此需要手动同步登录到镜像实例上。
 
--当登录未同步到镜像实例上时，如果镜像发生故障转移，则应用程序
--无法访问镜像数据库，镜像数据库上未与登录向管理的用户被称为
--孤立用户
 
--=========================================
--http://msdn.microsoft.com/zh-cn/library/ms174378.aspx
 
sp_change_users_login [ @Action = ] 'action'
    [ , [ @UserNamePattern = ] 'user' ]
    [ , [ @LoginName = ] 'login' ]
    [ , [ @Password = ] 'password' ]
[;]
--Auto_Fix
--将当前数据库的sys.database_principals 系统目录视图中的用户项链接到同名的SQL Server 登录名。如果不存在同名的登录名，将会创建一个。检查Auto_Fix 语句的结果，确认实际链接是否正确。在对安全性较为敏感的情况下，要避免使用Auto_Fix。
 
--如果使用Auto_Fix 时登录名尚不存在，则必须指定user 和password，否则必须指定user，但password 将被忽略。login 必须为NULL。user 必须是当前数据库中的有效用户。不能将另一个用户映射到该登录名。
 
--Report
--列出当前数据库中未链接到任何登录名的用户以及相应的安全标识符(SID)。user、login 和password 必须为NULL 或不指定。
 
--若要使用系统表通过某个查询替换报表选项，请将sys.server_prinicpals 中的条目与sys.database_principals 中的条目进行比较。
 
--Update_One
--将当前数据库中指定的user 链接到现有的SQL Server login。必须指定user 和login。password 必须为NULL 或不指定。
 
 
--=========================================
--镜像切换后
--查看孤立用户
USE REPDB
GO
EXEC sp_change_users_login 
@Action = 'REPORT',
@UserNamePattern = NULL,
@LoginName  = NULL,
@Password =  NULL
 
 
--=========================================
--创建登录[T1],并将T1和数据库[REPDB]中的用户[T1]关联
--该方式同样导致镜像两段的登录SID不同,从而导致孤立用户
USE [master]
GO
CREATE LOGIN [T1] WITH PASSWORD=N'T1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE REPDB
GO
EXEC sp_change_users_login 
@Action = 'Update_One',
@UserNamePattern = 'T1',
@LoginName  = 'T1'
 
--=========================================
--使用AUTO_FIX来解决
--该方式同样导致镜像两段的登录SID不同,从而导致孤立用户
USE REPDB
GO
EXEC sp_change_users_login 
@Action = 'Auto_Fix',
@UserNamePattern = 'T2',
@Password =  'T2'
 
--=========================================
--推荐做法
--在主库上查询得到需要同步的用户sid
USE master;
select sid,name from syslogins;
 
    --============================================
--在从库上创建登录
exec sp_addlogin
@loginame = 'DB1Login',
@passwd = 'Sql@123',
@sid=0x82873B5AFFEFE54EB4F9CA05C303C9AE