MS SQL Basics Tutorial: Role Management

Introduction to 14.5.1 Role management

New conceptual roles have been introduced since the release of SQL Server 7, replacing the concept of groups in previous versions. As with groups, SQL Server managers can set certain users to a role, so that only the permissions set on the role can be set for all user rights, greatly reducing the administrator's workload. There are two main types of roles in SQL Server: Server roles and database roles.

1 server Roles

The server role refers to dividing users with SQL Server management functions into different user groups based on the administrative tasks of SQL Server and the relative importance levels of these tasks, and each group has predefined permissions to administer SQL Server. The server role applies to the server scope and its permissions cannot be modified. For example, a user with the sysadmin role can perform any administrative work in SQL Server, and any attempt to modify its permissions will fail. This is different from the database role

There are 7 predefined server roles for SQL Server, and the specific meaning of each role is shown in table 14-3:

2 Database Roles

In SQL Server we often find that we want to grant a set of database-specific permissions to multiple users, but those users do not belong to the same NT group, or even though they can be classified by NT administrators as the same NT user group, unfortunately, we do not have the right to administer NT accounts. We can then add new database roles to the database or use existing database roles, and let those users with the same database permissions fall into the same role.

Thus, a database role can grant a user or group of users different levels of authority to manage or Access database or database objects, which are proprietary to the database. Also, you can make a user have multiple roles that belong to the same database.

SQL Server provides two types of database roles: predefined database roles, user-defined database roles.