sql server中的孤立用户

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

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

 

 

 

 

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

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

use db_name; 
exec   sp_change_users_login   'REPORT';

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

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

--也可以使用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 '

一般问题就会解决了。

 

如果多个用户出现孤立情况--可参考如下代码

--如果存在同名的登录用户就绑定，不存在就创建（创建时候密码为空）并绑定
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

 

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

--=========================================
--在镜像搭建后,在主库服务器上创建登录,并在数据库上建立对应用户,
--数据库中用户被同步到镜像数据库中,但登录是实例级对象,无法同步,
--因此需要手动同步登录到镜像实例上。
 
--当登录未同步到镜像实例上时，如果镜像发生故障转移，则应用程序
--无法访问镜像数据库，镜像数据库上未与登录向管理的用户被称为
--孤立用户
 
--=========================================
--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
 

 

转载于:https://www.cnblogs.com/gered/p/9213496.html