0

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.

3
  • 1
    I would expect KINGDOM\joker to be the database owner after they created the database, not sa or any other principal. Was the database owner changed after creation?
    – Dan Guzman
    Jun 28 at 21:17
  • It was not. Apparently KINGDOM\joker was mapped into the [model] DB by error, and so connected to all new DBs as KINGDOM\joker and not as dbo. Only the CREATE DATABASE permission is required to create. Now I just need to figure out how to let joker remove the backup and restore history when dropping the DB. Jun 29 at 18:47
  • When deleting the database through the SSMS and the "Delete backup and restore history" is checked, KINGDOM\joker receives a permission error, "The EXECUTE permission was denied on the object 'sp_delete_database_backuphistory' database msdb". I suppose I could give KINGDOM\joker permission on that object, but then would joker be able to delete the backup history of any DB? Jun 29 at 19:12

2 Answers 2

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]

enter image description here

1
  • Thanks. I should have tested better with a new user. I discovered that KINGDOM\joker had been mapped into the [model] DB (with public role). This may have been a mistake by someone who thought that joker should have had access to our modeling data when joker was first added as a user (note: we do not have a modeling DB). So whenever joker connected to [testDB] or any new DB, it was as joker and not dbo. Jun 29 at 18:38
0

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
1
  • Thanks. As noted above, I discovered that KINGDOM\joker had been mapped as a user into the [model] DB some time prior. This meant that KINGDOM\joker was connecting to every new database as KINGDOM\joker and never as dbo. I believe that whoever added joker to [model] may have thought that the [model] DB was something other than an important system DB. One of joker's primary roles in the company is computer modeling... This would normally be a bad security fault, but it got caught. Jun 29 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.