How to enable a SQL Server 2012 login to create, alter and drop the created databases?

I'm trying to assign a proper GRANT permission to CI login, that will be able to:

• Create test DB

• Alter it

• Drop this test DB (and only it)

But it appears that SQL Server 2012 GRANT CREATE ANY DATABASE only allows the creation, not the altering and dropping of the database (as the newly database does not hold the creator as dbo).

As just giving a sysadmin role is too large of a security risk, any idea what other GRANTS are required?

First you have to

-
Create Login with create database permission. Note that the login cannot drop any databases.

USE [master]
GO

CREATE LOGIN [Kin] WITH PASSWORD=N'somePassw0rd', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master]
GO
-- Grant create any database, but not drop or alter any database
 GRANT CREATE ANY DATABASE TO [Kin]
GO

-
Map the login to the database that you want to have permission to alter or drop.

-- for drop database, you just need db_owner
USE [test_drop]
GO
CREATE USER [Kin] FOR LOGIN [Kin] 
 GO

-
Add the user to the db_owner role.

EXEC sp_addrolemember 'db_owner', 'Kin'

Now test it for create / alter and drop :

EXECUTE AS LOGIN = 'Kin'

create database test_drop

EXECUTE AS LOGIN = 'Kin'
use test_drop

create table test (fname char(1))

EXECUTE AS LOGIN = 'Kin'
select * from test

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set offline

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set online

EXECUTE AS LOGIN = 'Kin'
drop database [test_drop]

Try to drop some other db :

EXECUTE AS LOGIN = 'Kin'
alter database [test_kin] 
set online

Below is the error :


Msg 5011, Level 14, State 9, Line 2
User does not have permission to alter database 'test_kin', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.

StackExchange Database Administrators Q#59748, answer score: 3