После перемещения базы данных (резервное копирование, восстановление) я должен повторно добавить пользователя

Я иногда перемещаю базу данных (SQL Express 2012) с машины разработки на сервер или наоборот, используя резервное копирование и восстановление базы данных в SQL Server Management Studio.

Всякий раз, когда я это делаю, приложения на целевом компьютере не могут обращаться к базе данных, пока я не удалю пользователя, которого они используют, «george», из пользователей базы данных (база данных, безопасность, пользователи в SQL Server Management Studio) добавьте его как владельца в разделе «Безопасность», «Вход», «george /properties», «User mapping».

Есть ли лучший способ сделать это? Это кажется немного запутанным.

39 голосов | спросил xpda 25 AMpThu, 25 Apr 2013 02:37:33 +040037Thursday 2013, 02:37:33

5 ответов


50

В этом разница между логинами и и как они соотносятся друг с другом:

  • Логины - Принципы уровня экземпляра, которые позволяют сущности подключаться к экземпляру SQL Server. Они по своей природе не предоставляют доступ к базам данных экземпляра. Исключением из этого является логин с правами sysadmin, который может использовать базу данных, потому что они являются sysadmin, но из-за разрешений уровня sysadmin.
  • Пользователи - участники уровня базы данных, которые позволяют сущности подключаться к базе данных SQL Server. Пользователи связаны с входами через SID, создавая взаимосвязь между ними и позволяя логину подключаться к экземпляру, а затем использовать связанного пользователя для подключения к базе данных.

Что обычно происходит с аутентифицированными входами SQL и пользователями базы данных при восстановлении, так это то, что SIDS будет не синхронизироваться, тем самым нарушая взаимосвязь. Эта связь должна быть восстановлена ​​до того, как вы сможете подключиться к базе данных с помощью этого входа, потому что в глазах SQL Server эти принципы больше не связаны. Вы можете исправить это со следующим SQL:

ALTER USER [foo] WITH LOGIN=[foo]

Вы можете использовать следующий запрос в контексте своей базы данных, чтобы проверить наличие сирот:

select
    dp.name [user_name]
    ,dp.type_desc [user_type]
    ,isnull(sp.name,'Orhphaned!') [login_name]
    ,sp.type_desc [login_type]
from   
    sys.database_principals dp
    left join sys.server_principals sp on (dp.sid = sp.sid)
where
    dp.type in ('S','U','G')
    and dp.principal_id >4
order by sp.name
ответил Mike Fal 25 AMpThu, 25 Apr 2013 02:52:29 +040052Thursday 2013, 02:52:29
2

Вы можете посмотреть на изменение базы данных на содержащуюся базу данных . Содержащийся пользователь базы данных аутентифицируется базой данных, а не на уровне экземпляра через логин. Это упрощает перемещение базы данных на другой экземпляр.

Если нет, вы можете создать резервную копию данных для входа, используя скрипты sp_help_revlogin, предоставленные в этом KB поддержки Microsoft , И выполните выходной скрипт в новом экземпляре.

ответил Travis Gan 25 AMpThu, 25 Apr 2013 03:10:43 +040010Thursday 2013, 03:10:43
2

Я использую приведенный ниже сценарий для миграции /создания учетных записей пользователей и логинов. Запустите его с сервера, на который вы восстановили базу данных, и укажите исходное имя сервера в качестве параметра процедуры.

Я не беру на себя ответственность за процедуру, поскольку я получил ее из других источников, но она отлично работает.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[stp_Admin_ReplicateUserLogins]    Script Date: 10/29/2015 08:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Procedure [dbo].[stp_Admin_ReplicateUserLogins] --'OriginalSourceDatabase', 1
    @PartnerServer sysname,
    @Debug bit = 0 -- 0 = Create Users, 1 = Display SQL command but doesn't execute query.
As

Declare @MaxID int,
    @CurrID int,
    @SQL nvarchar(max),
    @LoginName sysname,
    @IsDisabled int,
    @Type char(1),
    @SID varbinary(85),
    @SIDString nvarchar(100),
    @PasswordHash varbinary(256),
    @PasswordHashString nvarchar(300),
    @RoleName sysname,
    @Machine sysname,
    @PermState nvarchar(60),
    @PermName sysname,
    @Class tinyint,
    @MajorID int,
    @ErrNumber int,
    @ErrSeverity int,
    @ErrState int,
    @ErrProcedure sysname,
    @ErrLine int,
    @ErrMsg nvarchar(2048)
Declare @Logins Table (LoginID int identity(1, 1) not null primary key,
                        [Name] sysname not null,
                        [SID] varbinary(85) not null,
                        IsDisabled int not null,
                        [Type] char(1) not null,
                        PasswordHash varbinary(256) null)
Declare @Roles Table (RoleID int identity(1, 1) not null primary key,
                    RoleName sysname not null,
                    LoginName sysname not null)
Declare @Perms Table (PermID int identity(1, 1) not null primary key,
                    LoginName sysname not null,
                    PermState nvarchar(60) not null,
                    PermName sysname not null,
                    Class tinyint not null,
                    ClassDesc nvarchar(60) not null,
                    MajorID int not null,
                    SubLoginName sysname null,
                    SubEndPointName sysname null)

Set NoCount On;

If CharIndex('\', @PartnerServer) > 0
  Begin
    Set @Machine = LEFT(@PartnerServer, CharIndex('\', @PartnerServer) - 1);
  End
Else
  Begin
    Set @Machine = @PartnerServer;
  End

-- Get all Windows logins from principal server
Set @SQL = 'Select P.name, P.sid, P.is_disabled, P.type, L.password_hash' + CHAR(10) +
        'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals P' + CHAR(10) +
        'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.sql_logins L On L.principal_id = P.principal_id' + CHAR(10) +
        'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
        'And P.name <> ''sa''' + CHAR(10) +
        'And P.name Not Like ''##%''' + CHAR(10) +
        'and P.Name Not like ''NT SERVICE%''' + CHAR(10) +
        'And CharIndex(''' + @Machine + '\'', P.name) = 0;';

Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)
Exec sp_executesql @SQL;

-- Get all roles from principal server
Set @SQL = 'Select RoleP.name, LoginP.name' + CHAR(10) +
        'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_role_members RM' + CHAR(10) +
        'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals RoleP' +
        CHAR(10) + char(9) + 'On RoleP.principal_id = RM.role_principal_id' + CHAR(10) +
        'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals LoginP' +
        CHAR(10) + char(9) + 'On LoginP.principal_id = RM.member_principal_id' + CHAR(10) +
        'Where LoginP.type In (''U'', ''G'', ''S'')' + CHAR(10) +
        'And LoginP.name <> ''sa''' + CHAR(10) +
        'And LoginP.name Not Like ''##%''' + CHAR(10) +
        'And LoginP.name Not Like ''NT SERVICE%''' + CHAR(10) +
        'And RoleP.type = ''R''' + CHAR(10) +
        'And CharIndex(''' + @Machine + '\'', LoginP.name) = 0;';

Insert Into @Roles (RoleName, LoginName)
Exec sp_executesql @SQL;

-- Get all explicitly granted permissions
Set @SQL = 'Select P.name Collate database_default,' + CHAR(10) +
        '   SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,' + CHAR(10) +
        '   SubP.name Collate database_default,' + CHAR(10) +
        '   SubEP.name Collate database_default' + CHAR(10) +
        'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals P' + CHAR(10) +
        'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_permissions SP' + CHAR(10) +
        CHAR(9) + 'On SP.grantee_principal_id = P.principal_id' + CHAR(10) +
        'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals SubP' + CHAR(10) +
        CHAR(9) + 'On SubP.principal_id = SP.major_id And SP.class = 101' + CHAR(10) +
        'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.endpoints SubEP' + CHAR(10) +
        CHAR(9) + 'On SubEP.endpoint_id = SP.major_id And SP.class = 105' + CHAR(10) +
        'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
        'And P.name <> ''sa''' + CHAR(10) +
        'And P.name Not Like ''##%''' + CHAR(10) +
        'And P.name Not Like ''NT SERVICE%''' + CHAR(10) +
        'And CharIndex(''' + @Machine + '\'', P.name) = 0;'

Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)
Exec sp_executesql @SQL;



Select @MaxID = Max(LoginID), @CurrID = 1
From @Logins;

While @CurrID <= @MaxID
  Begin
    Select @LoginName = Name,
        @IsDisabled = IsDisabled,
        @Type = [Type],
        @SID = [SID],
        @PasswordHash = PasswordHash
    From @Logins
    Where LoginID = @CurrID;

    If Not Exists (Select 1 From sys.server_principals
                Where name = @LoginName)
      Begin
        Set @SQL = 'Create Login ' + quotename(@LoginName)
        If @Type In ('U', 'G')
          Begin
            Set @SQL = @SQL + ' From Windows;'
          End
        Else
          Begin
            Set @PasswordHashString = '0x' +
                Cast('' As XML).value('xs:hexBinary(sql:variable("@PasswordHash"))', 'nvarchar(300)');

            Set @SQL = @SQL + ' With Password = ' + @PasswordHashString + ' HASHED, ';

            Set @SIDString = '0x' +
                Cast('' As XML).value('xs:hexBinary(sql:variable("@SID"))', 'nvarchar(100)');
            Set @SQL = @SQL + 'SID = ' + @SIDString + ';';
          End

        If @Debug = 0
          Begin
            Begin Try
                Exec sp_executesql @SQL;
            End Try
            Begin Catch
                Set @ErrNumber = ERROR_NUMBER();
                Set @ErrSeverity = ERROR_SEVERITY();
                Set @ErrState = ERROR_STATE();
                Set @ErrProcedure = ERROR_PROCEDURE();
                Set @ErrLine = ERROR_LINE();
                Set @ErrMsg = ERROR_MESSAGE();
                RaisError(@ErrMsg, 1, 1);
            End Catch
          End
        Else
          Begin
            Print @SQL;
          End

        If @IsDisabled = 1
          Begin
            Set @SQL = 'Alter Login ' + quotename(@LoginName) + ' Disable;'
            If @Debug = 0
              Begin
                Begin Try
                    Exec sp_executesql @SQL;
                End Try
                Begin Catch
                    Set @ErrNumber = ERROR_NUMBER();
                    Set @ErrSeverity = ERROR_SEVERITY();
                    Set @ErrState = ERROR_STATE();
                    Set @ErrProcedure = ERROR_PROCEDURE();
                    Set @ErrLine = ERROR_LINE();
                    Set @ErrMsg = ERROR_MESSAGE();
                    RaisError(@ErrMsg, 1, 1);
                End Catch
              End
            Else
              Begin
                Print @SQL;
              End
          End
        End
    Set @CurrID = @CurrID + 1;
  End

Select @MaxID = Max(RoleID), @CurrID = 1
From @Roles;

While @CurrID <= @MaxID
  Begin
    Select @LoginName = LoginName,
        @RoleName = RoleName
    From @Roles
    Where RoleID = @CurrID;

    If Not Exists (Select 1 From sys.server_role_members RM
                Inner Join sys.server_principals RoleP
                    On RoleP.principal_id = RM.role_principal_id
                Inner Join sys.server_principals LoginP
                    On LoginP.principal_id = RM.member_principal_id
                Where LoginP.type In ('U', 'G', 'S')
                And RoleP.type = 'R'
                And RoleP.name = @RoleName
                And LoginP.name = @LoginName)
      Begin
        If @Debug = 0
          Begin
            Exec sp_addsrvrolemember @rolename = @RoleName,
                            @loginame = @LoginName;
          End
        Else
          Begin
            Print 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''',';
            Print '     @loginame = ''' + @LoginName + ''';';
          End
      End

    Set @CurrID = @CurrID + 1;
  End

Select @MaxID = Max(PermID), @CurrID = 1
From @Perms;

While @CurrID <= @MaxID
  Begin
    Select @PermState = PermState,
        @PermName = PermName,
        @Class = Class,
        @LoginName = LoginName,
        @MajorID = MajorID,
        @SQL = PermState + space(1) + PermName + SPACE(1) +
            Case Class When 101 Then 'On Login::' + QUOTENAME(SubLoginName)
                    When 105 Then 'On ' + ClassDesc + '::' + QUOTENAME(SubEndPointName)
                    Else '' End +
            ' To ' + QUOTENAME(LoginName) + ';'
    From @Perms
    Where PermID = @CurrID;

    If Not Exists (Select 1 From sys.server_principals P
                Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id
                Where SP.state_desc = @PermState
                And SP.permission_name = @PermName
                And SP.class = @Class
                And P.name = @LoginName
                And SP.major_id = @MajorID)
      Begin
        If @Debug = 0
          Begin
            Begin Try
                Exec sp_executesql @SQL;
            End Try
            Begin Catch
                Set @ErrNumber = ERROR_NUMBER();
                Set @ErrSeverity = ERROR_SEVERITY();
                Set @ErrState = ERROR_STATE();
                Set @ErrProcedure = ERROR_PROCEDURE();
                Set @ErrLine = ERROR_LINE();
                Set @ErrMsg = ERROR_MESSAGE();
                RaisError(@ErrMsg, 1, 1);
            End Catch
          End
        Else
          Begin
            Print @SQL;
          End
      End

    Set @CurrID = @CurrID + 1;
  End


Set NoCount Off;
ответил Peter Kudos 30 +03002015-10-30T12:34:40+03:00312015bEurope/MoscowFri, 30 Oct 2015 12:34:40 +0300 2015, 12:34:40
0

Вы всегда можете просто попытаться связать всех пользователей в базе данных с аналогичными именами на сервере базы данных.

ALTER 
    AUTHORIZATION
    ON 
        SCHEMA::db_owner
    TO 
        dbo
GO

DECLARE @username VARCHAR(64)
DECLARE @sql nvarchar(max)

DECLARE 
    UserCursor 
CURSOR FOR 
    SELECT 
        [name]
    FROM 
        sysusers
    WHERE 
        [name] NOT IN('dbo','guest','INFORMATION_SCHEMA','sys','public')
        AND 
        LEFT([name],3) <> 'db_' 
        AND 
        [name] NOT LIKE '%]%'

OPEN 
    UserCursor
FETCH NEXT 
FROM 
    UserCursor 
    INTO 
        @username
WHILE @@fetch_status <> -1
BEGIN
    SET @sql = 'ALTER USER [' + @username + '] WITH LOGIN=[' + @username + ']'
    PRINT @sql
    EXEC dbo.sp_executesql @sql;
    FETCH NEXT 
    FROM 
        UserCursor
        INTO 
            @username
END
CLOSE UserCursor
DEALLOCATE UserCursor
ответил NKCSS 13 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowWed, 13 Sep 2017 15:12:52 +0300 2017, 15:12:52
0

Здесь является решение, которое сработало для меня. Что он делает, так это:

  1. Список потерянных пользователей: EXEC sp_change_users_login 'REPORT'
  2. Исправить пользователей: EXEC sp_change_users_login 'UPDATE_ONE','<userName>','<userName>'
ответил misha 26 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowTue, 26 Sep 2017 11:17:20 +0300 2017, 11:17:20

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132