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

Question

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?

Answer 1

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.

Answer 2

Per the documentation for DROP DATABASE:

"Requires the CONTROL permission on the database, or ALTER ANY DATABASE permission, or membership in the db_owner fixed database role."

A possible way to handle this is to add the user to the model database as part of the db_owner role. Every database created in SQL Server is a copy of that and will retain all users, objects, grants, etc. This means that whenever a new database is created, it will retain your CI user in the db_owner role and have permission to drop/alter that database. The drawback, of course, is that this will carry over to databases not created as part of your CI process as well. You would have to go in and manually remove that if you didn't want this.

Otherwise, your CI login will need high enough permissions to create and drop any database, as the hurdles to restrict this start to become extremely complex.