4

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?

  • Erland Sommarskog has an excellent write-up. But basically, EXECUTE AS OWNER is the owner of the database and usually does not have server level permissions. But this should point you in the right direction. sommarskog.se/grantperm.html#execasserver – Jonathan Fite May 21 at 12:05
  • 1
    The answer below by 'Solomon Rutzky' looks really good, but if you need the database regularly, why don't you just leave it in place. You can drop and add tables as needed. – James Jenkins May 21 at 17:11
7

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

  • 1
    This solution would pass the database audit I am doing now which uses CIS standards, bravo! – kevinsky May 21 at 17:11
  • This works brilliantly, thank you very much. – XCom69 May 30 at 6:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.