SQL Server 中的角色管理

SQL Server通过Server Roles来进行整个Server的权限管理，通过Database Roles来对单个的数据库进行权限管理。

通过将用户加到不同的Server Role组来控制用户对SQL Server的操作权限，首先，每一个SQL Server的用户都有public这个Server Role的权限，以下是SQL Server Security下除public外所有预定义Server Role所对应的权限表示：

•SysAdmin: Any member can perform any action on the server.
•ServerAdmin: Any member can set configuration options on the server.
•SetupAdmin: Any member can manage linked servers and SQL Server startup options and tasks.
•Security Admin: Any member can manage server security.
•ProcessAdmin: Any member can kill processes running on SQL Server.
•DbCreator: Any member can create, alter, drop, and restore databases.
•DiskAdmin: Any member can manage SQL Server disk files.
•BulkAdmin: Any member can run the bulk insert command.

而对单个数据库的权限管理，我们使用Database Roles来实现，以下是所有预定义的Database Role所对应的权限表示：

•db_owner: Members have full access.
•db_accessadmin: Members can manage Windows groups and SQL Server logins.
•db_datareader: Members can read all data.
•db_datawriter: Members can add, delete, or modify data in the tables.
•db_ddladmin: Members can run dynamic-link library (DLL) statements.
•db_securityadmin: Members can modify role membership and manage permissions.
•db_bckupoperator: Members can back up the database.
•db_denydatareader: Members can’t view data within the database.
•db_denydatawriter: Members can’t change or delete data in tables or views.

除了预定义的Database Roles, 我们也可以自定义Database Role。