SQL Server Database Ownership

Question

Server: SQL Azure Managed instance

I want to setup the DB correctly , via DB Roles/Schema's ;

What I want to do

Create a specific Login without any extra permissions + Deny Connect, One database owner per server.
I want to approach this using Principle of least privilege.

Example :

Database1 owned by DBOwner
Database2 owned by DBOwner
Database3 owned by DBOwner

”Why not sa?” -- makes little sense that the highest privileged account in SQL Server
With regard to security it is totally on contrary to the Principle of least privilege.

Question
Does Microsoft include a special “DBOwner” Account at server level by default, which not only pre-exists and has no permissions

In Azure the Vulnerability report suggest ALTER ROLE [db_owner] DROP MEMBER [dbo]

You cannot drop dbo from db_owner. There's a hard-wired blocker in the engine prohibiting you to do so. Try and you get the error:

Msg 15405, Level 16, State 1, Line 1
Cannot use the special principal 'dbo'.

I am a confused as how to resolve this issue or if it is an issue , what is best practice

---------------------------

Vulnerability Report

---------------------------

Description
SQL Server provides roles to help manage the permissions. Roles are security principals that group other principals. Database-level roles are database-wide in their permission scope. This rule checks that a minimal set of principals are members of the fixed database roles.

Remediation
Remove members who should not have access to the database role

Impact
Fixed database roles may have administrative permissions on the system. Following the principle of least privilege, it is important to minimize membership in fixed database roles and keep a baseline of these memberships. See https://learn.microsoft.com/en-us/sql...se-level-roles for additional information on database roles.

SELECT user_name(sr.member_principal_id) as [Principal]
,user_name(sr.role_principal_id) as [Role]
,type_desc as [Principal Type]
FROM sys.database_role_members AS sr
INNER JOIN sys.database_principals sp ON sp.principal_id = sr.member_principal_id
WHERE sr.role_principal_id IN (user_id('bulkadmin'),
user_id('db_accessadmin'),
user_id('db_securityadmin'),
user_id('db_ddladmin'),
user_id('db_backupoperator'),
user_id('db_owner'))

Result

Princilpe is Dbo
Role: Db_Owner
Principle type EXTERNAL_USER

Answer 1

You cannot drop dbo from db_owner. There's a hard-wired blocker in the engine prohibiting you to do so... Flagging dbo is a bug the assessment tool. The purpose of the assessment is to make sure one evaluates whether other members need those permissions.". Source: DBA Stackexchange.

Answer 2

I read that already , contradicts the approach MS recommend, Principle of least privilege, the dbo is not

Reason I was trying to implement Login without any extra permissions + Deny Connect, One database owner per server.

What MS recommends, MS does not allow you do ; frustrating and confusing

The dbo, or database owner, is a user account that has implied permissions to perform all activities in the database. Members of the sysadmin fixed server role are automatically mapped to dbo.

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/server-and-database-roles-in-sql-server

Answer 3

I agree with Mr. Morillo, " There are many things that do not apply to Azure SQL. Microsoft documentation says that Azure SQL and SQL Server are the same, but they are not. They are really different. The list of things that you can do on SQL Server and you cannot do on Azure SQL is large."