3. SERVER AND DATABASE ROLES

Question

Tell me everything i need to know about roles in MSSQL and some example code

Answer 1

Roles in SQL Server are security principals used to group permissions and simplify management. There are server-level roles, database-level roles, and special-purpose roles in certain system databases and services.

1. Server-level roles

Server-level roles control permissions at the SQL Server instance level.

Fixed server-level roles

Fixed server-level roles are predefined and have specific capabilities. Examples from the context:

• processadmin: Can end processes that are running in an instance of SQL Server.
• setupadmin: Can add and remove linked servers by using Transact-SQL statements (sysadmin membership is needed when using Management Studio).
• bulkadmin: Can run the BULK INSERT statement. Members can potentially elevate their privileges under certain conditions; apply least privilege and monitor activity. Not supported for SQL Server on Linux, and bulk operations are not supported for Microsoft Entra logins except via sysadmin.
• diskadmin: Used for managing disk files.
• dbcreator: Can create, alter, drop, and restore any database.
• public: Every SQL Server login belongs to this role. When a server principal is not granted or denied specific permissions on a securable object, it inherits permissions granted to public on that object. Membership in public cannot be changed, but permissions can be granted, denied, or revoked from public.

Additional fixed server-level roles introduced in SQL Server 2022:

• ##MS_DatabaseManager##: Can create and delete databases. The creator becomes the database owner (dbo) of the new database and has all database permissions in that database. Members do not necessarily have access to databases they do not own. This role has the same privileges as dbcreator but is preferred because it also exists in Azure SQL Database.
• ##MS_ServerStateManager##: Has the same permissions as ##MS_ServerStateReader## plus ALTER SERVER STATE, allowing access to management operations such as DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE('ALL'), DBCC SQLPERF().
• ##MS_ServerStateReader##: Can read all DMVs and functions covered by VIEW SERVER STATE, and has VIEW DATABASE STATE on any database where the member has a user account.
• ##MS_ServerPerformanceStateReader##: Can read DMVs and functions covered by VIEW SERVER PERFORMANCE STATE, and has VIEW DATABASE PERFORMANCE STATE on any database where the member has a user account. This is a subset of ##MS_ServerStateReader## to better support least privilege.

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

Example: Add a login to a server-level role (Azure SQL / SQL Server 2022-style)

Run in the master (or virtual master in Azure SQL Database):

-- Add login Jiao to server-level role ##MS_ServerStateReader##
ALTER SERVER ROLE ##MS_ServerStateReader##
    ADD MEMBER Jiao;
GO

To list members of server-level roles (Azure SQL example):

SELECT srm.role_principal_id, r.name AS RoleName,
       srm.member_principal_id, l.name AS MemberName
FROM sys.server_role_members AS srm
JOIN sys.server_principals AS r
    ON srm.role_principal_id = r.principal_id
JOIN sys.sql_logins AS l
    ON srm.member_principal_id = l.principal_id;

2. Database-level roles

Database-level roles are defined in each database and control permissions within that database.

• Fixed database roles are predefined per database.
• Members of the db_owner database role can manage fixed database role membership.
• Any database user and other database roles can be added to database-level roles.
• Server-level permissions cannot be granted to database roles.
• Logins and other server-level principals cannot be added directly to database roles; they must first be mapped to database users.

User-defined database roles can be created and customized using GRANT, DENY, and REVOKE. It is recommended not to add user-defined database roles as members of fixed roles, to avoid unintended privilege escalation.

Example: Create and use a database role

USE MyDatabase;
GO

-- Create a user-defined database role
CREATE ROLE ReportingReaders;
GO

-- Grant permissions to the role
GRANT SELECT ON SCHEMA::dbo TO ReportingReaders;
GO

-- Create a user mapped to a login
CREATE USER ReportUser FROM LOGIN ReportLogin;
GO

-- Add the user to the role
ALTER ROLE ReportingReaders ADD MEMBER ReportUser;
GO

Special-purpose roles in msdb

The msdb database contains special-purpose roles used by SQL Server components:

• db_ssisadmin, db_ssisoperator, db_ssisltduser: Administer and use SQL Server Integration Services (SSIS).
• dc_admin, dc_operator, dc_proxy: Administer and use the data collector.
• PolicyAdministratorRole: Administer Policy-Based Management policies and conditions.
• ServerGroupAdministratorRole, ServerGroupReaderRole: Administer and use registered server groups.
• dbm_monitor: Created when the first database is registered in Database Mirroring Monitor; has no members until assigned.

Security note: Members of db_ssisadmin and dc_admin might be able to elevate privileges to sysadmin because they can modify SSIS packages, which can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To mitigate this, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges, or only add sysadmin members to these roles.

Example: Add a user to an msdb role

USE msdb;
GO

-- Add user to SSIS admin role
ALTER ROLE db_ssisadmin ADD MEMBER MySsisAdminUser;
GO

3. Role behavior and permission inheritance

• Server roles grant server-level permissions; database roles grant database-level permissions.
• Example: ##MS_ServerStateReader## holds VIEW SERVER STATE. A login in this role that has user accounts in master and another database automatically has VIEW DATABASE STATE in those databases by inheritance.
• public is a special server role; all logins are members. Only grant public permissions when an object should be available to all users.

4. Permissions and best practices

• Use roles to group permissions and assign users to roles rather than granting permissions directly to users.
• Apply the principle of least privilege, especially for powerful roles like dbcreator, ##MS_DatabaseManager##, bulkadmin, and SSIS/data collector roles.
• Monitor activity of members of powerful roles.

---

References:

• Database-level roles
• Server-level roles
• Azure SQL Database server roles for permission management
• SQL Server 2022 : Issue Restoring Database from 2014 Fails Without 'sysadmin' Role - Microsoft Q&A
• how to copy usermappings from one to another - Microsoft Q&A
• Unable to do database backups due to user account permission - Microsoft Q&A