sqlserver SQL Server Management Studio和Transact-SQL创建账户、创建访问指定数据库的只读用户

前言

---

1. Create a Login

sqlserver创建用户
Transact-SQL创建用户

文档中介绍了using SQL Server Management Studio (SSMS) 和Transact-SQL2种方式，本人创建只读账户使用的第一种方式；

---

2. 创建访问指定数据库的只读用户

   （1）使用Microsoft SQLServer Management Studio，用管理员账户登录

   （2）点击【安全性】->【登录名】，选择【新建登录名】

   （3）在【常规】选项【登录名】中输入用户名 cz。身份验证选择【SQL Server 身份验证】，输入密码，取消勾选【强制实施密码策略】

   （4）点击选项页【用户映射】->【映射到此登录名的用户】，选择创建的cz用户可操作的数据库。针对每一个数据库，在下方【数据库角色成员身份】中勾选成员身份，即可获取身份对应的权限，如只读权限，即勾选【db_datareader】。同时【默认架构】中亦勾选【db_datareader】

   （6）只读用户cz创建成功

连接成功：

修改数据报错：

---

备注：服务器角色

bulkadmin check box
Members of the bulkadmin fixed server role can run the BULK INSERT statement.

dbcreator check box
Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

diskadmin check box
Members of the diskadmin fixed server role can manage disk files.

processadmin check box
Members of the processadmin fixed server role can terminate processes running in an instance of the Database Engine.

public check box
All SQL Server users, groups, and roles belong to the public fixed server role by default.

securityadmin check box
Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.

serveradmin check box
Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.

setupadmin check box
Members of the setupadmin fixed server role can add and remove linked servers, and they can execute some system stored procedures.

sysadmin check box
Members of the sysadmin fixed server role can perform any activity in the Database Engine.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

---