主页 / dba / 问题 / 193138
Accepted
Shaul Behr
Asked: 2017-12-15 06:25:46 +0800 CST

为什么用户无权访问数据库？

  • 772

我有一个用户ls_readonly应该db_datareader对多个数据库具有特权。我以为我设置正确：

在此处输入图像描述

但是当我连接到服务器ls_readonly并尝试在对象资源管理器中打开数据库时，出现错误：

数据库 wtest 不可访问。（对象浏览器）

我打开一个查询窗口master并尝试运行：

use wtest

这回应：

Msg 916, Level 14, State 1, Line 1
The server principal "ls_readonly" is not able to access the database "wtest" under the current security context.

我错过了什么？

更新：这是一个线索。ls_readonly如果我作为用户从数据库的安全上下文中删除，那么我会转到服务器安全上下文下的用户并在“用户映射”下授予对数据库的访问权限，然后它开始工作。

可能是该数据库最初是从另一台服务器恢复的，该服务器也有一个ls_readonly用户。我猜那用户标识不是基于用户名？

sql-server permissions

4 个回答

  1. Best Answer

    可能是数据库最初是从另一台服务器恢复的，该服务器也有一个 ls_readonly 用户。我猜那么用户标识不是基于用户名？

    通常情况下，所有登录名都sid与相应的用户相同。这就是CREATE USER FROM LOGIN命令的意义：数据库主体是使用与sid相应登录名相同的（安全标识符）创建的。

    该用户sid现在存储在您的数据库中，并且当您backup/restore您的数据库时，sid 也被保留。

    现在想象一下，您有 2 台服务器，并且它们都已ls_readonly登录。为简单起见，在第一台服务器ls_readonly上sid= 1，在第二台服务器上sid= 2。您拥有映射此登录名的数据库 MyDB。在第一台服务器上 MyDB 存储sid= 1，在第二台服务器上sid= 2。

    您在第一台服务器上备份 MyDB 数据库并在第二台服务器上恢复它。现在您的登录sid名 = 2，在数据库 MyDB 中sid= 1。

    你登录ls_readonly并尝试访问MyDB，服务器正在检查数据库中是否有sid = 2，没有，所以服务器ls_readonly根本没有映射到MyDB。

    这可以通过做来解决

    alter user ls_readonly with login = ls_readonly;

    这个命令只是用登录的 sid 更新数据库的ls_readonly用户 sid ls_readonly。保留所有权限。

    如果您通过删除它来重新创建用户，一旦删除该用户，授予它的所有权限都将被删除，您需要重新授予它们。

    • 16

  2. @sepupic很好地涵盖了我建议的所有内容，但我想我会分享以下我运行的脚本，每当我恢复从一个 SQL Server 实例备份并恢复到不同实例的数据库时。在刚刚恢复的数据库中运行它。它基本上自动化了@sepupic 所说的内容。（execute注释掉了）

    SET NOCOUNT ON

DECLARE @cmd nVARCHAR(max) = ''

SELECT @Cmd = @cmd + 'ALTER USER ' + dp.NAME + ' WITH LOGIN = ' + dp.NAME + ';' + CHAR(10)
FROM sys.database_principals dp
LEFT JOIN master.sys.server_principals sp ON sp.NAME = dp.NAME
WHERE dp.type = 's'
    AND dp.default_schema_name <> dp.NAME
    AND sp.NAME IS NOT NULL
ORDER BY dp.NAME

PRINT @Cmd
--EXECUTE sp_executeSQL @CMD
    • 6

  3. 这听起来像一个孤立的用户。孤立用户是用户 SID 与登录 SID 不匹配的地方，而不是我们看到的友好名称（可能是由于从服务器上删除了登录名，或者从不同的服务器恢复了数据库）。您不需要删除用户并重新创建它，因为所有现有权限都会丢失（好吧，这只是角色，但假设您对其他对象拥有单独的权限；存储过程和表等）。有一个功能允许您将用户重新映射到登录名（即使它是同名的）。

    use db1
GO
alter user [user_name] with login = [login_name]

    所以它想：

    use wtest
GO
alter user [ls_readonly] with login = [ls_readonly]

    有关MS 文档中的更多详细信息，请参见此处

    • 5

  4. 从数据库中删除用户
    删除并重新创建用户 将用户
    添加到数据库

    你可能不需要第二个，但它不应该伤害

    • -2

相关问题