SQL Server Interview Question #184

What are server roles and database roles?

Security, Backup, Recovery, Administration & Real-World Scenarios Senior Advanced

Detailed Explanation

Server roles group permissions at SQL Server instance scope, while database roles group permissions inside a database.

Fixed server roles include powerful roles such as sysadmin, so membership must be tightly controlled. Database roles include built-in roles and custom roles. Custom roles are often preferable when an application requires a specific permission set rather than broad built-in privileges.

Code Example

USE SalesDb;
CREATE ROLE ReportingRole;
GRANT SELECT ON SCHEMA::dbo TO ReportingRole;
ALTER ROLE ReportingRole ADD MEMBER AppUser;