liveBook · Manning

Fixed server role

As explained in 100 SQL Server Mistakes and How to Avoid Them

A fixed server role is a predefined SQL Server server-level role with a defined set of permissions.

A fixed server role is a predefined SQL Server server-level role with defined permissions. Treat membership as a security decision, not a convenience setting. section 14.1

Mistake: granting sysadmin because a task sounds administrative.

  • The sysadmin fixed server role provides full access to the instance, allowing its members to do anything. section 14.1
  • An application team may request sysadmin merely to create and delete databases on a nonproduction server. section 14.1
  • A vendor account with sysadmin on every instance creates a major security hole: compromise could expose organizational data. section 14.1
  • Apply least privilege: grant only the permissions required for the task, temporarily adding and then revoking extra access. section 14.1
Listing 14.1 Creating a custom server role
CREATE SERVER ROLE AvailabilityGroupsFailover ;
GO

ALTER SERVER ROLE AvailabilityGroupsFailover ADD MEMBER [NT 
AUTHORITY\SYSTEM] ;

GRANT ALTER ANY AVAILABILITY GROUP TO AvailabilityGroupsFailover ;

GRANT CONNECT SQL TO AvailabilityGroupsFailover ;

GRANT CREATE AVAILABILITY GROUP TO AvailabilityGroupsFailover ;

GRANT VIEW SERVER STATE TO AvailabilityGroupsFailover ;

Use a custom server role when a narrowly defined operational task needs server-level permissions without full sysadmin access. section 14.1

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Defines fixed roles, explains sysadmin consequences, and demonstrates a custom-role alternative.
Examples
Uses application teams, third-party tools, compromised vendor accounts, and an availability-groups custom role.
What it assumes
Assumes basic SQL Server access concepts while teaching why broad permissions violate least privilege.
What you can do after
Operate/configure access by replacing broad sysadmin grants with scoped server-role permissions.
How it connects
Connects fixed server roles with least privilege, SQL Server permissions, sysadmin, and custom server roles.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.