SQL还原数据库后孤立用户问题处理

最新推荐文章于 2020-11-29 13:41:22 发布

gaotianle

最新推荐文章于 2020-11-29 13:41:22 发布

阅读量689

点赞数

文章标签：数据库 sql sql server login action user

本文链接：https://blog.csdn.net/gaotianle/article/details/4041587

版权

在项目部署的时候，经常为了方便，直接备份还原数据库文件，如果在备份的数据库里有新建的数据库访问用户账号，还原之后不能使用，还没法删除。这样的数据库用户称为孤立用户。所谓孤立帐户，就是某个数据库的帐户只有用户名而没有登录名，这样的用户在用户库的sysusers系统表中存在，而在master数据库的syslogins中却没有对应的记录。

那如何解决这个用户的问题呢？放在哪里不管又有点碍眼。

1、最笨的解决办法：
在备份的时候就直接把新建的用户删除，这样还原的时候就是干净的，在部署的服务器上新建一个用户就可以了。

2、修复原来的用户，修复之后可以继续使用或者删除重建
在SQL里可以使用系统的存储过程sp_change_users_login来解决这个问题【sp_change_users_login 不能用于 Windows 登录名。】
sp_change_users_login [ @Action = ] 'action' [ , [ @UserNamePattern = ] 'user' ] [ , [ @LoginName = ] 'login' ] [ , [ @Password = ] 'password' ]

使用 sp_change_users_login 将当前数据库中的数据库用户链接到 SQL Server 登录名。如果用户登录名已更改，则使用 sp_change_users_login 将用户链接到新的登录，而不会丢失用户的权限。新的 login 不能为 sa，而 user 不能为 dbo、guest 或 INFORMATION_SCHEMA 用户。

[ @Action = ] 'action'

说明过程要执行的操作。action 的数据类型为 varchar(10)。action 可具有下列值之一。

值	说明
Auto_Fix	将当前数据库的 sysusers 表中的用户条目链接到同名的 SQL Server 登录名。如果不存在同名的登录名，将会创建一个。检查 Auto_Fix 语句的结果，确认实际链接是否正确。在对安全性较为敏感的情况下，要避免使用 Auto_Fix。如果使用 Auto_Fix 时登录名尚不存在，则必须指定 user 和 password，否则必须指定 user，但 password 将被忽略。login 必须为 NULL。user 必须是当前数据库中的有效用户。不能将另一个用户映射到该登录名。
Report	列出当前数据库中未链接到任何登录名的用户以及相应的安全标识符 (SID)。user、login 和 password 必须为 NULL 或不指定。
Update_One	将当前数据库中的指定 user 链接到现有 SQL Server login。必须指定 user 和 login。password 必须为 NULL 或不指定。

值

说明

Auto_Fix

将当前数据库的 sysusers 表中的用户条目链接到同名的 SQL Server 登录名。如果不存在同名的登录名，将会创建一个。检查 Auto_Fix 语句的结果，确认实际链接是否正确。在对安全性较为敏感的情况下，要避免使用 Auto_Fix。

如果使用 Auto_Fix 时登录名尚不存在，则必须指定 user 和 password，否则必须指定 user，但 password 将被忽略。login 必须为 NULL。user 必须是当前数据库中的有效用户。不能将另一个用户映射到该登录名。

Report

列出当前数据库中未链接到任何登录名的用户以及相应的安全标识符 (SID)。user、login 和 password 必须为 NULL 或不指定。

Update_One

将当前数据库中的指定 user 链接到现有 SQL Server login。必须指定 user 和 login。password 必须为 NULL 或不指定。

[ @UserNamePattern = ] 'user'
当前数据库中的用户名。user 的数据类型为 sysname，默认值为 NULL。

[ @LoginName = ] 'login'
SQL Server 登录的名称。login 的数据类型为 sysname，默认值为 NULL。
[ @Password = ] 'password'
通过指定 Auto_Fix 创建的新 SQL Server 登录名分配的密码。如果已存在匹配的登录名，则映射该用户名与登录名且忽略 password。如果不存在匹配的登录名，则 sp_change_users_login 创建新的 SQL Server 登录名并分配 password 作为新登录名的密码。password 的数据类型为 sysname，且不能为 NULL。

示例代码：

A. 显示登录映射的当前用户的报告列出当前数据库的孤立用户
EXEC sp_change_users_login 'Report';

B. 将数据库用户映射到新的 SQL Server 登录名
在以下示例中，数据库用户与新的 SQL Server 登录名关联。数据库用户 MB-Sales 首先映射到另一个登录名，然后重新映射到登录名 MaryB。
--Create the new login.
CREATE LOGIN MaryB WITH PASSWORD = '982734snfdHHkjj3';
GO
--Map database user MB-Sales to login MaryB.
USE AdventureWorks;
GO
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
GO

C. 自动将用户映射到登录名（必要时新建一个登录名）
以下示例显示如何使用 Auto_Fix 将现有用户映射到同名的登录名，以及如何在不存在登录名 Mary 的情况下，创建密码为 B3r12-3x$098f6 的 SQL Server 登录名 Mary。USE AdventureWorks;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';
GO