Muokkaa

Jaa

Create a Server Role

  • Artikkeli

Applies to: SQL Server Not supported. Azure SQL Database Not supported. Azure Synapse Analytics Analytics Platform System (PDW)

This topic describes how to create a new server role in SQL Server by using SQL Server Management Studio or Transact-SQL.

In This Topic

Before You Begin

Limitations and Restrictions

Server roles cannot be granted permission on database-level securables. To create database roles, see CREATE ROLE (Transact-SQL).

Security

Permissions

  • Requires CREATE SERVER ROLE permission or membership in the sysadmin fixed server role.

  • Also requires IMPERSONATE on the server_principal for logins, ALTER permission for server roles used as the server_principal, or membership in a Windows group that is used as the server_principal.

  • When you use the AUTHORIZATION option to assign server role ownership, the following permissions are also required:

    • To assign ownership of a server role to another login, requires IMPERSONATE permission on that login.

    • To assign ownership of a server role to another server role, requires membership in the recipient server role or ALTER permission on that server role.

Using SQL Server Management Studio

To create a new server role

  1. In Object Explorer, expand the server where you want to create the new server role.

  2. Expand the Security folder.

  3. Right-click the Server Roles folder and select New Server Role....

  4. In the New Server Role -server_role_name dialog box, on the General page, enter a name for the new server role in the Server role name box.

  5. In the Owner box, enter the name of the server principal that will own the new role. Alternately, click the ellipsis (...) to open the Select Server Login or Role dialog box.

  6. Under Securables, select one or more server-level securables. When a securable is selected, this server role can be granted or denied permissions on that securable.

  7. In the Permissions: Explicit box, select the check box to grant, grant with grant, or deny permission to this server role for the selected securables. If a permission cannot be granted or denied to all of the selected securables, the permission is represented as a partial select.

  8. On the Members page, use the Add button to add logins that represent individuals or groups to the new server role.

  9. A user-defined server role can be a member of another server role. On the Memberships page, select a check box to make the current user-defined server role a member of a selected server role.

  10. Select OK.

Using Transact-SQL

To create a new server role

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    --Creates the server role auditors that is owned the securityadmin fixed server role.  
USE master;  
CREATE SERVER ROLE auditors AUTHORIZATION securityadmin;  
GO

For more information, see CREATE SERVER ROLE (Transact-SQL).