主页 / dba / 问题 / 134301
Accepted
Hikari
Asked: 2016-04-05 06:31:25 +0800 CST

拒绝用户在 master 中创建表的权限

  • 772

我注意到我可以在主数据库中创建表、模式、存储过程等。

我不知道是什么授予了这个权限。这很烦人，因为当我们在 SSMS 中打开 *.sql 文件时，它默认与master相关。如果在CREATE没有注意到并设置正确的数据库上下文的情况下执行操作，则在master中创建表。

如果我们没有权限，我们会得到一个错误，注意到它并更改数据库上下文。有了权限，表就被创建了，我们稍后才看到错误，需要删除它并重新创建。我注意到master中已经有一些错误创建的表。

我想撤销每个人类用户的许可，但我害怕破坏某些东西。我不知道某些 SQL Server 例程是否需要在那里创建的权限。

在不破坏 SQL Server的情况下撤销master的创建权限的最佳方法是什么？

sql-server sql-server-2012

1 个回答

  1. Best Answer

    您需要确定登录名如何有权在 master 中运行 DDL。以下查询将返回具有服务器级角色成员身份的登录列表：

    SELECT ServerName = @@SERVERNAME
    , RoleName = roles.name
    , MemberName = members.name
    , IsEnabled = CASE WHEN members.is_disabled = 1 THEN 0 ELSE 1 END
FROM sys.server_role_members srm
    INNER JOIN sys.server_principals roles ON srm.role_principal_id = roles.principal_id
    INNER JOIN sys.server_principals members on srm.member_principal_id = members.principal_id
WHERE members.name NOT LIKE 'NT %' --exclude builtin principals
ORDER BY roles.name
    , members.name;

    您可以使用xp_logininfo以下命令了解特定 Windows 帐户如何访问 SQL Server：

    EXEC xp_logininfo @acctname = 'DOMAIN\Username', @option = 'all';

    如果相关人员具有该sysadmin角色的成员资格，您将无法阻止他们在master（或其他任何地方）运行 DDL 语句。

    假设这些人不是sysadmin服务器角色的成员，那么DENY权限优先于已经拥有的权限GRANTed。

    您可以创建一个名为“NoMasterDDL”的 Windows 安全组，并将您的开发人员添加到该组。为组创建登录名，然后DENY对您不希望人们在 master 中执行的 DDL 语句的权限。

    USE master;
CREATE LOGIN [DOMAIN\NoMasterDDL] FROM WINDOWS;
CREATE USER [DOMAIN\NoMasterDDL] FOR LOGIN [DOMAIN\NoMasterDDL];
DENY ALTER ON DATABASE::master TO [DOMAIN\NoMasterDDL];

    以上将阻止[DOMAIN\NoMasterDDL]组成员执行任何 DDL 类型的语句，例如CREATE TABLE.

    正如我在回答您之前的问题时所说的，AMtwo在评论中提到：

    如果用户当前是sysadmin的成员，则任何DENY权限都将无效。特别是在开发环境中，开发人员可能具有这样的访问权限。如果是这样，CONTROL SERVER可以使用权限代替；它与sysadmin相同，但服从DENY权限。

    因此，您可以授予CONTROL SERVER而不是使它们成为sysadmin服务器角色的成员，然后DENY ALTER如上所示使用以防止主数据库中的 DDL。

    • 7

相关问题