SQL Server Security: Best Practices for db_accessadmin, db_securityadmin, and db_ddladmin Roles

Understanding SQL Server roles is crucial for managing permissions and ensuring SQL Server security. In this post, we will delve into three specific roles: db_accessadmin, db_securityadmin, and db_ddladmin, discussing when each should be used and considerations for least privilege and security. We’ll also include a script you can use to audit your database roles.

db_accessadmin

The db_accessadmin role is responsible for adding or removing access to the database for Windows logins, Windows groups, and SQL Server logins. This role is typically used by Database Administrators (DBAs) who manage security. Normal users should not be a member of this role. Applications should tend not to need this role.

Security Considerations: Since this role can control who has access to the database, it’s important to limit its use to prevent unauthorized access. Regular audits of role membership can help identify any exceptions.

db_securityadmin

Members of the db_securityadmin role can modify role membership for custom roles only and manage permissions. This role is designed for server principals who will administer security. Normal users should not be a member of this role. Applications should tend not to need this role.

Security Considerations: Members of this role have the permissions to elevate their privileges, so their actions should be monitored perhaps adding an audit or trigger to notify a team once sysadmin or any explicit privileges are granted would be good for auditing those in this role. They can create logins and assign them permissions greater than their own. Therefore, restricting membership of the db_securityadmin role to only necessary members is recommended.

db_ddladmin

The db_ddladmin role can create, drop, and alter objects within the database, regardless of who the owner is. It is not unusual to grant this role to developers in a non-production environment. Normal users should not be a member of this role. Applications should not need this role.

Security Considerations: The db_ddladmin role cannot alter security. However, since this role can modify database objects, it’s important to ensure that it’s used responsibly to prevent unintended changes to the database structure.

Auditing SQL Server Role memberships:

Please use this script to assist in auditing these three elevated roles on your databases, as always – test and understand these scripts in a non-production environment, and understand that changing roles may cause processes relying on the permissions to break.

-- Find Database-Level Role Memberships 
IF OBJECT_ID('tempdb..#dbElevated') IS NOT NULL
    DROP TABLE #dbElevated

CREATE TABLE #dbElevated (
    Database_Name NVARCHAR(2560)
    , database_role_name NVARCHAR(100)
	, server_user_name  NVARCHAR(256)
	, [Role Removal Script - Be EXTREMELY careful] NVARCHAR(1000)
    )

INSERT #dbElevated
EXEC sp_MSforeachdb 'USE ? 
SELECT ''?'' AS [Database name]
,	DP1.name AS database_role_name
,	isnull(DP2.name, ''No members'') AS server_user_name  
, ''USE ''+ ''?'' + ''; EXEC sp_droprolemember [''+DP1.name+''], [''+DP2.name+''];  '' AS [Role Removal Script - Be EXTREMELY careful]
FROM sys.database_role_members AS DRM  
RIGHT OUTER JOIN sys.database_principals DP1  
	ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.database_principals DP2  
	ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.name IN (''db_ddladmin'',''db_securityadmin'',''db_accessadmin'')
AND DP2.name NOT LIKE ''%dbo%''
ORDER BY DP1.name; '

SELECT * FROM #dbElevated

In conclusion, understanding these roles and their appropriate usage is key to maintaining a secure and well-managed SQL Server environment. Always remember the principle of least privilege: only grant the minimum permissions necessary for a user to perform their duties. Regular audits of role membership and permissions can help maintain security and catch any potential issues early.