Execute Permissions for Stored Procedure Creating Databases in SQL Server

impersonationpermissionsSecuritysql serverstored-procedures

I have a Stored Procedure that creates a Database for a specific project which needs to run every month by one of our Data Analysts. The question is how do I structure this to enable the Analyst to run this Stored Procedure without granting them Create Database Permission.

I Tried
WITH EXECUTE AS OWNER/USER_NAME

The Analysts still get the below error:
Msg 262, Level 14, State 1, Line 67
CREATE DATABASE permission denied in database 'master'.

Any advice?

Best Answer

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

Related Question