SQL Server Blog

The Database Owner Role in SQL Server: A Call for Least Privilege

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.


In the world of SQL Server, the Database Owner role is a powerful one. It’s a role that carries with it a great deal of responsibility, as those who are a part of this role have permission to perform any activity within a database, up to and including dropping the database. We should have a very short list of who has this role in our environments.

Understanding the Database Owner Role

The Database Owner role, or db_owner, is a role that has implied permissions to perform all activities in the database. This includes creating and dropping tables, adding users, and granting or revoking permissions. Essentially, the db_owner has the keys to the kingdom.

The Principle of Least Privilege

The principle of least privilege is a computer security concept in which a user is given the minimum levels of access necessary to complete his or her job functions. This principle is important in maintaining the security of a database system. When applied to SQL Server, this means that a user should be given only the permissions they need to perform their tasks, and no more. Using less privileged built-in roles or, ideally, building a custom role with the bare minimum or needed permissions is one of the best security decisions you can make for your environment.

Risks of Misusing the Database Owner Role

Assigning too many users to the database owner role, or assigning the wrong users, can lead to several potential risks:

  • Unintended Access: Users with the db_owner role can access all data and execute all functions in the database. This could lead to unauthorized access to sensitive data.
  • Accidental Damage: With the ability to modify the structure of the database, a user with the db_owner role could accidentally drop tables or delete important data.
  • Security Threats: If a user’s account is compromised, having db_owner permissions could allow an attacker to inflict maximum damage to the database.

Best Practices for Assigning the Database Owner Role

Given these risks, it’s important to follow best practices when assigning the database owner role:

  • Limit Assignments: The db_owner role should be assigned to as few users as possible. Ideally, only one user should have this role.
  • Use Custom Roles: Instead of assigning users to the db_owner role, create custom roles that have only the necessary permissions.
  • Regular Audits: Regularly review who has been assigned the db_owner role to ensure that it is still appropriate. The script I’ve shared below will help with this.

The script I’ve prepared here will give you every member of the db_owner role across databases. This can be the basis of an access audit where you work with your team to reduce the permissions of users and roles to just what they need, rather than full and unrestricted control of the database:

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

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

INSERT #dbOwners
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 LIKE ''%db_owner%'' 
AND DP2.name NOT LIKE ''%dbo%''
ORDER BY DP1.name; '

SELECT * FROM #dbOwners


The db_owner role is a powerful in SQL Server, but it must be used wisely. By following the principle of least privilege, we can ensure that users have the access they need without exposing the database to unnecessary risks. Remember, security isn’t just about keeping the bad guys out; it’s also about ensuring that the good guys have the appropriate permissions to do their jobs and avoiding dangerous situations where over provisioned permissions cause issues for your business.

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