Grant user rights to create new databases but deny rights to existing ones

Question

I got a request from our developers that,

• They need a new user that need rights to create new databases
• Alter those but should not be able to access the existing ones.

What's best practice for this kind of access?

Grant dbcreator and deny access on existing ones or is there a better way?

Answer 1

The SQL Server dbcreator doesn't give you rights to access databases by default. It might seem that way because when you create a new database, you are (often, unless specified otherwise) automatically owner of said database. And the owner of a database can do everything in said database.

But if a login has the server role of dbcreator, they can still drop databases that they do not own. They can create new databases or alter existing ones.

If your intention is to purely allow them to create new databases and manage only these. You should only give them the create permission.

USE MASTER
GO
GRANT CREATE ANY DATABASE TO Foo
GO

This still allows them to run ALTER statements to the databases they create (because they are owner of said databases). But not run alter (or drop) statements against existing databases.

USE MASTER
GO
CREATE DATABASE [OwnedDB] --Succeeds
GO
ALTER DATABASE [OwnedDB] SET RECOVERY SIMPLE WITH NO_WAIT --Succeeds
GO
ALTER DATABASE [UnownedDB] SET RECOVERY SIMPLE WITH NO_WAIT --Fails due to permissions
GO