Связывание пользователей с Login после восстановления базы данных SQL Server 2005

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

Однако в нашем приложении используется много пользователей базы данных, которые определены в базе данных. Они должны быть связаны с логинами, которые определены в основной базе данных. На сервере, на котором я восстановил базу данных, были определены все логины, однако у них разные стороны.

Я не эксперт T-SQL...

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

Сценарии создания базы данных, которые мы используем для нашего приложения, создаем пользователей и логинов, однако при создании логина он не указывает идентификатор SID, поэтому эта проблема. Теперь, если бы у меня была машина времени...

(Когда я Google получаю много хитов, однако они в основном являются сайтами, которые не позволят вам увидеть ответ, не регистрируясь на сайте в первую очередь.)

Ответ 1

Это явление называется "осиротевшими пользователями".

Вот статья об этом и как ее исправить:

http://sqlblog.com/blogs/eric_johnson/archive/2008/10/17/fixing-orphaned-users.aspx

И здесь статья "Книги онлайн" о команде:

http://msdn.microsoft.com/en-us/library/aa259633(SQL.80).aspx

Ответ 2

Да, вы можете сделать это, выполнив:

EXEC sp_change_users_login 'Auto_Fix' , 'TheUserName';

Однако, если у вас был вопрос, могу ли я исправить всех пользователей автоматически, тогда это не будет сделано.

Ответ 3

Я придумал следующее. Он отлично работает, потому что он показывает вам:

Все текущие осиротевшие пользователи.
Какие из них были исправлены.
Какие из них не могут быть исправлены.

Другие решения требуют, чтобы вы знали имя осиротевшего пользователя перед тем, как это исправить.

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

Script:

EXEC sp_change_users_login 'report'--See all orphaned users in the database.
DECLARE @OrphanedUsers TABLE
(
  IndexKey Int IDENTITY(1,1) PRIMARY KEY,
  UserName SysName,--nVarChar(128)
  UserSID  VarBinary(85)
)
INSERT INTO @OrphanedUsers
    EXEC sp_change_users_login 'report'

DECLARE @CRLF as nVarChar
    SET @CRLF = CHAR(10) + '&' + CHAR(13)--NOTE: Carriage-Return/Line-Feed will only appear in PRINT statements, not SELECT statements.
DECLARE @Sql as nVarChar(MAX)
    SET @Sql = N''
DECLARE @IndexKey as Int
    SET @IndexKey = 1
DECLARE @MaxIndexKey as Int
    SET @MaxIndexKey = (SELECT COUNT(*) FROM @OrphanedUsers)
DECLARE @Count as Int
    SET @Count = 0
DECLARE @UsersFixed as nVarChar(MAX)
    SET @UsersFixed = N''
DECLARE @UserName as SysName--This is an orphaned Database user.

WHILE (@IndexKey <= @MaxIndexKey)
  BEGIN
    SET @UserName = (SELECT UserName FROM @OrphanedUsers WHERE IndexKey = @IndexKey)
    IF 1 = (SELECT COUNT(*) FROM sys.server_principals WHERE Name = @UserName)--Look for a match in the Server Logins.
      BEGIN
        SET @Sql = @Sql + 'EXEC sp_change_users_login ''update_one'', [' + @UserName + '], [' + @UserName + ']' + @CRLF
        SET @UsersFixed = @UsersFixed + @UserName + ', '
        SET @Count = @Count + 1
      END
    SET @IndexKey = @IndexKey + 1
  END

PRINT @Sql
EXEC sp_executesql @Sql
PRINT   'Total fixed: ' + CAST(@Count as VarChar) + '.  Users Fixed: ' + @UsersFixed
SELECT ('Total fixed: ' + CAST(@Count as VarChar) + '.  Users Fixed: ' + @UsersFixed)[Fixed]
EXEC sp_change_users_login 'report'--See all orphaned users still in the database.

Результат:

* Примечание. 4, которые не были исправлены (в моем примере выше, на скриншоте), не было соответствующего пользователя на целевом сервере, на который была восстановлена база данных.

Ответ 4

Я нашел следующий script из Microsoft KB918992 - запустил его на исходном сервере и создаст хранимую процедуру, называемую sp_help_revlogin ', который генерирует еще один script для запуска на целевом сервере, создавая все учетные записи пользователей с одинаковыми паролями и sid. Разработал чудеса для нашего обновления с SQL 2000 до 2008 года.

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(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
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 @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name = @login_name
OPEN login_curs 
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
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 ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr 
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
        PRINT @tmpstr 
      END
      ELSE BEGIN -- NT login has access
        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
        PRINT @tmpstr 
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN 
        -- Null password
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
      ELSE 
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr 
    END
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs 
DEALLOCATE login_curs 
RETURN 0
GO

Ответ 5

У меня есть приятный script, который вы можете использовать для создания логинов пользователей базы данных, с которыми я столкнулся после поиска этой проблемы, этот script использует хранимую процедуру. вы можете найти и другие полезные скрипты здесь также по этому URL-адресу http://www.sqlserveroptimizer.com/2011/08/how-to-script-logins-from-user-database-in-sql-server-20052008-r2/

USE MyDatabaseName

DECLARE @login nvarchar(50)

DECLARE logins_cursor CURSOR FOR SELECT l.name FROM sys.database_principals u INNER JOIN sys.server_principals l ON u.sid=l.sid

OPEN logins_cursor FETCH NEXT FROM logins_cursor INTO @login

WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_help_revlogin @login FETCH NEXT FROM logins_cursor INTO @login END

CLOSE logins_cursor DEALLOCATE logins_cursor GO

Ответ 6

Если:

EXEC sp_change_users_login 'Auto_Fix' , 'TheUserName';

Не работайте, попробуйте следующее:

EXEC sp_change_users_login 'Auto_Fix', 'Username', NULL, '[email protected]'

Я нашел его здесь: http://dbadiaries.com/using-sp_change_users_login-to-fix-sql-server-orphaned-users