SQL Server Blog

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

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.

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.


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.


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.


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.

David Seis
Article by David Seis
David Seis is a Support Technician that joined Straight Path in the summer of 2021. His goal is to work in an area that he believes helps others, and to do it to the best of his ability. He loves to help, solve problems, and make things easy for others to understand. Because of this he is lovingly known as a “Good helper” among friends and family. Outside of work, he has a growing passion for cybersecurity, specifically helping protect those who are most vulnerable to fraud, scams, and phishing. He looks forward to learning more about each of his clients, helping minimize issues, and ensure that any problems are solved quickly.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This