6.3 Server Roles

6.3 Server Roles

6.3.1 fixed server role

To facilitate the management of permissions on the server, SQL Server provides several roles, which are security principals that are used to group other principals (SQL Server logins, Windows accounts, and Windows groups). Roles are similar to groups in the Microsoft Windows operating system.

SQL Server provides nine fixed server roles. The permissions granted to the fixed server role cannot be changed.

(1) The sysadmin

Members of this role can perform any activity on the server.

(2) serveradmin

Members of this role can change server-wide configuration options and shut down the server.

(3) securityadmin

Members of this role can manage logins and their properties, including GRANT, DENY, and REVOKE server-level permissions. This role can also GRANT, DENY, and REVOKE database-level permissions if they have access to the database. In addition, you can reset the password for the SQL Server login.

(4) Processadmin

A member of this role can terminate a process that runs in an instance of SQL Server.

(5) Setupadmin

Members of this role can use Transact-SQL statements to add and remove linked servers. (SysAdmin membership is required when using Management Studio.) ）

(6) Bulkadmin

Members of this role can run the BULK INSERT statement.

(7) Diskadmin

This role is used to manage disk files.

(8) dbcreator

Members of this role can create, change, delete, and restore any database.

(9) Public

Each SQL Server logon name belongs to the public server role. Public is implemented in a different way than other roles. However, permissions can be granted, denied, or revoked from public.

If a server principal is not granted or denied specific permissions to a securable object, the user inherits the permissions granted to the public role of the object. When you want the object to be available to all users, you only need to assign public permissions to any object.

You cannot change a member relationship in public.

You can use the Sys.fn_builtin_permissions system function to list server-level permissions.

SELECT * from sys.fn_builtin_permissions (' SERVER ') ORDER by Permission_name;

You can query the membership of the current role by using the following T-SQL statement (the public role is not shown in the results).

SELECT srm.role_principal_id, sp.name as Role_name, srm.member_principal_id, Sp2.name as Member_name From Sys.server_role_members as SRM JOIN Sys.server_principals as SP On SRM. role_principal_id = sp.principal_id JOIN Sys.server_principals as SP2 On srm.member_principal_id = sp2.principal_id ORDER by Sp.name, Sp2.name

6.3.2 user-defined server roles

SQL Server 2012 and later versions can create user-defined server roles and add server-level permissions to user-defined server roles.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/79/02/wKiom1aE4vSQq-DHAABwM_-2YmU943.png "style=" float: none; "title=" New role 1.png "alt=" Wkiom1ae4vsqq-dhaabwm_-2ymu943.png "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/79/00/wKioL1aE4xPRqACpAABa0l58L1A952.png "style=" float: none; "title=" New role 2.png "alt=" Wkiol1ae4xprqacpaaba0l58l1a952.png "/>

A user-defined server role can change the name or delete it. Requires the ALTER ANY server role permission on the server to change the name of the user-defined server roles.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/79/01/wKioL1aE43GA6bWkAAB1xRUCwbI944.png "title=" Remove the role. png "alt=" Wkiol1ae43ga6bwkaab1xrucwbi944.png "/>

Examples of T-SQL syntax are:

CREATE SERVER ROLE CusRole1; ALTER SERVER ROLE CusRole1 with NAME = Cusrolenewname;

6.3.3 adding or removing members

You can add server-level principals (SQL Server logins, Windows accounts, and Windows groups) to server-level roles. Each member of the fixed server role can add additional logins to the same role. a member of a user-defined server role cannot add additional server principals to the role.

To add members to a fixed server role, you must be a member of the fixed server role or a member of the sysadmin fixed server role. The CONTROL server and alter any SERVER role permissions are not sufficient to perform alter server roles for the fixed server, and you cannot grant ALTER permission to the fixed server role.

Unlike fixed server roles, the members of a user-defined server role do not have the right to add members to the same role. To add members to a user-defined server role, you must be a member of the sysadmin fixed server role, or have CONTROL SERVER or ALTER any server roles permissions. Otherwise, you must have ALTER permission on the role.

When you create a login, you can add or remove server roles for the user at the same time.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/79/00/wKiom1aE38-TRusPAABZhpzOENU528.png "title=" Server role. png "alt=" Wkiom1ae38-truspaabzhpzoenu528.png "/>

In SSMS, expand Security, server roles, and edit the properties of a server role. You can add a login or server role by clicking the Add button, and clicking the Delete button removes the currently selected member from the role.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/79/01/wKiom1aE4MSSiATSAADnx6lHq8c703.png "title=" Add member. png "alt=" Wkiom1ae4mssiatsaadnx6lhq8c703.png "/>

Examples of T-SQL syntax are:

ALTER SERVER ROLE serveradmin ADD MEMBER [Sqlsvr2014\localuser1]; ALTER SERVER ROLE diskadmin ADD MEMBER SqlUser1; ALTER SERVER ROLE serveradmin DROP MEMBER [Sqlsvr2014\localuser1]; ALTER SERVER ROLE diskadmin DROP MEMBER SqlUser1;

Tips:

The use of sp_addsrvrolemember and sp_dropsrvrolemember stored procedures is not recommended.

This article from "SQLServer2014 series" blog, declined reprint!

6.3 Server Roles