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';
USE [master] GO SELECT SUSER_SNAME(owner_sid) AS login FROM sys.databases WHERE name = 'testDB';
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]?
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.