将Azure数据库配置为只读 [英] Configuring Azure db as read-only

问题描述

是否可以将Azure sql数据库临时配置为只读.在尝试创建只读用户时，我运行了以下

Is there a way to configure an Azure sql database as read-only temporarily. In my attempt to create a readonly user, I've ran the following

CREATE LOGIN reader WITH password='****' -- successful
CREATE USER readerUser FROM LOGIN reader; -- successful
EXEC sp_addrolemember 'db_datareader', 'readerUser';  -- failed 

最后一条语句失败，并显示错误

and the last statement failed with the error

无法更改角色"db_datareader"，因为它不存在或 您没有权限.

Cannot alter the role 'db_datareader', because it does not exist or you do not have permission.

推荐答案

如果您真的想使用上述命令添加只读用户(以便可以以只读模式或读写方式连接模式，具体取决于指定了要连接的用户)，以下是在执行上述命令时可能出了什么问题的说明.

If you really wanted to add a read only user with the commands that you have mentioned above (so that you can connect in read-only mode or read-write mode depending on which user is specified for connection), here is an explanation of what probably went wrong, when you executed the above mentioned commands.

CREATE LOGIN reader WITH password='****' -- successful
CREATE USER readerUser FROM LOGIN reader; -- successful
EXEC sp_addrolemember 'db_datareader', 'readerUser';  -- failed

无法更改角色"db_datareader"，因为它不存在或 您没有权限.

Cannot alter the role 'db_datareader', because it does not exist or you do not have permission.

当我在主数据库上直接执行您提到的所有3条命令时，能够重现与您相同的错误.

I was able to reproduce the same error as you got when I executed all 3 commands that you mentioned directly on the master database.

理想情况下，要创建一个只读用户，必须使用主数据库创建登录名，以便它是服务器级登录，然后在要使用的数据数据库上进行用户创建和添加角色成员.

Ideally, to create a read-only user, you have to create the login using master database, so that it's server level login, and then user creation and role member addition will happen on the data database that you intend to use.

-- Run this command on Master Database
CREATE LOGIN reader WITH password='****'  

-- Run these commands on data Database
CREATE USER readerUser FROM LOGIN reader;  
EXEC sp_addrolemember 'db_datareader', 'readerUser'; 

-- Optionally, run an extra command to create the same user on Master database. This command is not required if you connect only from code/api's, but if you want to connect to your database using SQL Management Studio, then you need this one.
CREATE USER readerUser FROM LOGIN reader; -- run this on Master Database again

这篇关于将Azure数据库配置为只读的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！