如何在SQL Server中一次性授予对多个架构的访问权限 [英] How to grant access to multiple schemas in one go in SQL Server

问题描述

在SQL Server数据库管理方面，我非常环保。

I'm very green when it comes to the world of database management in SQL Server.

下面是我用于创建登录名&的SQL代码。用户和授予权限

Below is my SQL code to create a login & user & grant permissions

USE TestDb
GO

CREATE LOGIN [TestLogin] 
       WITH PASSWORD = N'123', DEFAULT_DATABASE = [TestDb], 
       CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO

CREATE USER SqlUser FOR LOGIN [TestLogin]

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: dbo  to SqlUser

罚款&创建用户/登录。他可以访问dbo模式。

This runs fine & create user/login. He can access the dbo schema.

但是我需要在 SCHEMA 选项中指定多个架构。

But I need to specify multiple schemas in the SCHEMA options.

所以我尝试了：

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: [dbo,app]  to SqlUser

但是我得到一个错误：

找不到模式'dbo; app'，因为它不存在或您没有权限。

Cannot find the schema 'dbo;app', because it does not exist or you do not have permission.

如何在上述SQL模式中指定多个模式以授予用户访问权限？

How can I specify multiple schemas in the above SQL pattern to grant access to the user?

谢谢！ / p>

Thanks!

推荐答案

您将必须运行多个语句：

You would have to run multiple statements:

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SqlUser
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::app TO SqlUser

如果您经常这样做，或者有多个用户需要这些权限，那么最好创建 ROLE ，然后将用户添加到该 ROLE 。这样一来，您便可以使用 ALTER ROLE 代替。

If this is something you do regularly, or several users need these permissions, you'd be better off creating a ROLE, and then adding users to that ROLE. This will enable you to do it in a single statement, with ALTER ROLE instead.

CREATE ROLE SqlRole; --Give a better, more appropriate name
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SqlRole;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::app TO SqlRole;

GO

ALTER ROLE SqlRole ADD MEMBER SqlUser;

这篇关于如何在SQL Server中一次性授予对多个架构的访问权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！