liveBook · Manning

SQL Server permissions

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

SQL Server permissions are authorization grants that control which operations a principal can perform on SQL Server securable objects.

Mistake #1: accepting sysadmin as the default fix. A principal should receive only the permissions needed for daily work; temporary privileges should be granted for a specific task and then revoked. section 14.1

The vendor account that can reach everything

An application team may request sysadmin rights to create and delete databases on a nonproduction server. A backup, monitoring, or security vendor may demand sysadmin on every instance. That account would have unrestricted access; if compromised, it could expose organizational data. Treat the request as a permissions-design problem, not an automatic approval. 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 to grant only the required server-level capabilities, such as availability-group operations, SQL connectivity, creation rights, and server-state visibility. section 14.1

Listing 14.2 Database role to allow stored procedure execution
USE model ;
GO
 
CREATE ROLE ExecSP ;
GO

GRANT EXECUTE TO ExecSP ;

Use a database role when the requirement is limited to executing stored procedures rather than controlling the entire SQL Server instance. section 14.1

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

How deep it goes
Explains least privilege through risks and provides server- and database-role configuration examples.
Examples
Uses application teams, backup tools, monitoring tools, security tools, and an unrestricted vendor account as risk scenarios.
What it assumes
Assumes basic awareness of SQL Server accounts while clarifying how role scope addresses common permission misunderstandings.
What you can do after
Configure scoped roles instead of granting unrestricted sysadmin access.
How it connects
Connects principals with securable objects, fixed server roles, custom server roles, database roles, and stored procedure execution.
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

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.