What are the Minimum Permissions to Create an MSSQL Database and Take Ownership of it?

Question

I would like a less privileged user (KINGDOM\joker) to be able to create, manage, and drop databases on an MSSQL 2017 server [14.0.2027.2 (X64)]. KINGDOM\joker should only be able to affect the databases that they create, and should not be able to drop, restore, or take ownership of other databases.

I granted KINGDOM\joker the CREATE DATABASE and MSSQL specific CREATE ANY DATABASE permissions. Using SQL Server Management Studio (v18, v19), KINGDOM\joker can create a new database [testDB] but the dbo in [testDB] is 'sa' and KINGDOM\joker cannot take ownership, despite KINGDOM\joker being the recorded owner in the master table.

USE [testDB]
GO
SELECT name,sid,SUSER_SNAME(sid) AS login FROM sys.database_principals WHERE name = 'dbo';

name	sid	login
dbo	0x01	sa

USE [master]
GO
SELECT SUSER_SNAME(owner_sid) AS login FROM sys.databases WHERE name = 'testDB';

login
KINGDOM\joker

ALTER AUTHORIZATION ON DATABASE::testDB to "KINGDOM\joker";

Fails with permission denied.

As I understand, [testDB] is create from [model] and the dbo in [model] is 'sa'. I expected the dbo in [testDB] to be changed to KINGDOM\joker by the server when it creates [testDB] from [model]. It seems to have once worked that way but MS changed the behavior with SQL Server 2016, and the MS community post that explained this change is now an invalid link.

Is there some MSSQL Server option or setting, or some new MS-specific permission that will allow the owner_sid in sys.databases to ALTER or IMPERSONATE the dbo in [testDB]?

OR

Any other work-around or method to accomplish the objective described in the first paragraph? BTW, I have considered adding KINGDOM\joker as a user in [model] and assigning the db_owner role, but that would affect every new database.

Answer 1

All is well. This query is just misleading:

SELECT name,sid,SUSER_SNAME(sid) AS login FROM sys.database_principals WHERE name = 'dbo';

DBO always has a SID of 0x01 regardless of who owns the database. The database owner is the login whose SID is in owner_sid in master.sys.databases.

Think of it this way. SA always maps to DBO. But all sysadmins and the database owner always connect as DBO.

In any case, when KINGDOM\joker connects to the database, he will be connected as DBO since it's his database.

Eg, after

create login [Office\joker] from windows
grant create database to [Office\joker]

Answer 2

Can you produce a repro on this?

I agree with Dan's comment, that the owner has likely been changed since creation of the database.

If you look at my repro below, you will see that dbo should be the sid that owns the database. I.e., if you see something different then we would have to determine what is different in how we do things (or the owner was changed, perhaps by a nightly job applying some best practices thinking).

USE master
GO
CREATE LOGIN [ww\Pelle] FROM WINDOWS
GO
GRANT CREATE DATABASE TO [ww\Pelle]
GO

From a command prompt:

runas /user:pelle cmd.exe

From command prompt created above:

1> create database pelle1
2> go
1> use pelle1
2> go
Changed database context to 'pelle1'.
1> select user_name()
2> go
                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------
dbo   

From SSMS:

USE pelle1
SELECT p.name, p.sid FROM sys.database_principals AS p WHERE name = 'dbo'

name    sid
dbo 0x010500000000000515000000840152F3AD7526D67071FE26ED030000