还原 SQL Server 2005 数据库后将所有用户链接到登录 [英] Linking ALL Users to Login after restoring a SQL Server 2005 database

问题描述

(注意这个问题询问链接所有用户，不像询问链接单个用户的可能重复)

(Note this question asks about linking ALL Users, unlike the possible duplicate that asks about linking a single user)

我希望在两台服务器之间移动数据库，我已经从第一台服务器备份了数据库，并在第二台服务器上进行了数据库恢复，到目前为止一切顺利.

I wish to move a database between two servers, I have backed the database up from the first server and done a database restore on the 2nd server, so far so good.

然而，我们的应用程序使用了许多在数据库中定义的数据库用户.这些必须链接到在 master 数据库中定义的登录名.我将数据库恢复到的服务器定义了所有登录名，但是它们有不同的 sid.

However our application makes use of a lot of database users that are defined in the database. These have to be linked to logins that are defined in the master database. The server I have restored the database to has all the logins defined, however they have different sids.

我不是 T-SQL 专家......

I am not a T-SQL expert….

我认为 sp_change_users_login 是解决方案的一部分，但我不知道如何让它自动将恢复的数据库中的所有用户链接到同名.

I think sp_change_users_login is part of the solution, but I can't find out how to get it to automatically link all users in the restored database to the login of the same name.

我们用于应用程序的数据库创建脚本创建用户和登录名，但是在创建登录名时它没有指定 SID，因此出现了这个问题.现在如果我有一台时光机...

The database creation scripts we use for our application create the users and logins, however it does not specify the SID when creating the login, hence this problem. Now if I had a time machine...

(当我使用 Google 搜索时，我获得了很多点击率，但它们大多是网站，如果您不先在网站上注册，就不会让您看到答案.)

(When I Google I get lots of hits, however they are mostly sites that won't let you see the answer without having to register on the site first.)

推荐答案

我想出了以下内容.效果很好，因为它向您展示了:

I came up with the following. It works great because it shows you:

1. 所有当前的孤立用户.
2. 修复了哪些问题.
3. 哪些无法修复.

其他解决方案需要您事先知道孤立的用户名才能进行修复.

Other solutions require you to know the orphaned user name before hand in order to fix.

以下代码可以在将数据库恢复到另一台服务器后调用的 sproc 中运行.

The following code could run in a sproc that is called after restoring a database to another server.

脚本:

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 个(在我上面的示例截图中)在数据库恢复到的目标服务器中没有对应的用户.

*Note: The 4 that were not fixed (in my example screenshot above) did not have a corresponding User in the destination Server that the database was restored to.

这篇关于还原 SQL Server 2005 数据库后将所有用户链接到登录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！