Execute Permissions for a Store Procedure that creates databases

It did not work because the EXECUTE AS clause of a CREATE {object} statement can only reference a User, which is a database-level principal. CREATE DATABASE is an instance-level permission, so it needs to be granted to a login (an instance-level principal), not to a user.

This is easy to accomplish using Module Signing:

SETUP

USE [tempdb]; CREATE LOGIN [DbCreator] WITH PASSWORD='create a DB'; CREATE USER [DbCreator] FOR LOGIN [DbCreator]; EXEC(N'CREATE PROCEDURE dbo.CreateDatabase ( @NewDatabaseName sysname ) AS SET NOCOUNT ON; DECLARE @SQL NVARCHAR(MAX); SET @SQL = N''CREATE DATABASE '' + QUOTENAME(@NewDatabaseName); EXEC(@SQL); '); GRANT EXECUTE ON dbo.[CreateDatabase] TO [DbCreator]; 

APPLY MODULE SIGNING

-- 1) Create the Certificate: CREATE CERTIFICATE [Permission$CreateDatabase] ENCRYPTION BY PASSWORD = 'UseBetterPassword!' WITH SUBJECT = 'CREATE DATABASE permission', EXPIRY_DATE = '2099-12-31'; -- 2) Sign the Module: ADD SIGNATURE TO [dbo].[CreateDatabase] BY CERTIFICATE [Permission$CreateDatabase] WITH PASSWORD = 'UseBetterPassword!'; -- 3) Backup the Private Key to a VARBINARY string -- to be copied and pasted somewhere safe (optional): SELECT CERTPRIVATEKEY(CERT_ID(N'Permission$CreateDatabase'), 'NewPassword!', 'UseBetterPassword!'); -- 4) Remove the Private Key (optional): ALTER CERTIFICATE [Permission$CreateDatabase] REMOVE PRIVATE KEY; -- 5) Copy Certificate to [master] (Public Key only): DECLARE @Cert NVARCHAR(4000) = CONVERT(NVARCHAR(4000), CERTENCODED(CERT_ID(N'Permission$CreateDatabase')), 1); EXEC (N'USE [master]; CREATE CERTIFICATE [Permission$CreateDatabase] FROM BINARY = ' + @Cert); -- 6) Create Login and Grant Permission: EXEC (N'USE [master]; CREATE LOGIN [Permission$CreateDatabase] FROM CERTIFICATE [Permission$CreateDatabase]; GRANT CREATE ANY DATABASE TO [Permission$CreateDatabase];'); 

TEST

EXECUTE AS LOGIN = N'DbCreator'; SELECT SESSION_USER; -- DbCreator CREATE DATABASE [_TEST:FAIL]; /* Msg 262, Level 14, State 1, Line XXXXX CREATE DATABASE permission denied in database 'master'. */ EXEC dbo.[CreateDatabase] N'_TEST:PASS'; -- Success!! SELECT * FROM sys.databases WHERE [name] = N'_TEST:PASS'; REVERT; SELECT SESSION_USER; -- dbo DROP DATABASE [_TEST:PASS]; 

For a detailed explanation of the steps taken to apply module signing, please see my post:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level

For more information about module signing in general, and why you should use it instead of the easier yet more dangerous SET TRUSTWORTHY ON, please see my post:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining