HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlModerate

How to give a user "create any database" permission in SQL Server?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
createsqluseranypermissiongivedatabasehowserver

Problem

Is it different from the dbcreator permission?

Can anybody explain the process?

Solution

The Server-Level Roles documentation says:

Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

This obviously exceeds the ability to just "create any database" mentioned in the title of your question.

To add a login to the dbcreator role, use ALTER SERVER ROLE:

ALTER SERVER ROLE dbcreator
    ADD MEMBER server_principal;


The process to grant only the permission CREATE ANY DATABASE is as simple as:

-- Must be in master to grant server-scoped permissions
USE master;
GRANT CREATE ANY DATABASE TO server_principal;


It is generally seen as best practice to grant only the minimum permissions required.

Code Snippets

ALTER SERVER ROLE dbcreator
    ADD MEMBER server_principal;
-- Must be in master to grant server-scoped permissions
USE master;
GRANT CREATE ANY DATABASE TO server_principal;

Context

StackExchange Database Administrators Q#112833, answer score: 15

Revisions (0)

No revisions yet.