SQL Server Security – CREATE DATABASE Permission Denied for Sysadmin

Securitysql serversql-server-2008-r2

I'm attempting to create a stored procedure that can be used as a proxy for creating databases where users do not have rights for creating databases. See Restricting Database Access/Functions to User Groups? for more info

When I attempt to execute the stored proc, SQL Server generates a security error:

Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.

This is the code:

CREATE LOGIN DatabaseCreator WITH PASSWORD='Pa$$w0rd'; /* REPLACE WITH A SECURE PASSWORD! */
GO
CREATE USER DatabaseCreator FOR LOGIN DatabaseCreator;
GO
EXEC sys.sp_addsrvrolemember 'DatabaseCreator','sysadmin';
GO
CREATE PROCEDURE dbo.CreateDatabase
(
    @DatabaseName SYSNAME
)
WITH EXECUTE AS 'DatabaseCreator'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cmd NVARCHAR(max);
    IF COALESCE(@DatabaseName,'') <> ''
    BEGIN
        SET @cmd = 'CREATE DATABASE ' + QUOTENAME(@DatabaseName) + ';' 
        EXEC sys.sp_executesql @cmd;
    END
    ELSE
    BEGIN
        SET @cmd = 'INVALID DATABASE NAME';
        RAISERROR (@cmd, 0, 1);
    END
END;
GO
EXEC master.dbo.CreateDatabase 'MyDatabaseName';

msg 262 appears to be a generic Permission Denied message. Since DatabaseCreator has sysadmin privileges, this should not be happening. Unless I'm missing something.

Best Answer

The EXECUTE AS clause of CREATE PROCEDURE can only be used to impersonate a user (not a login) and the scope of impersonation is restricted to the current database. The sysadmin permission is associated with the login, not the user, so you receive a permissions error.

The correct way to grant CREATE DATABASE here is to sign the procedure. The process is a little involved because we want to sign the procedure to grant a server-level permission, but each step of the process is reasonably simple:

First, create a certificate and login in master with the CREATE ANY DATABASE permission. The certificate will later be copied to the target database to allow procedure signing.

USE master;
GO
CREATE CERTIFICATE CreateDatabaseCert
   ENCRYPTION BY PASSWORD = 'password'
   WITH SUBJECT = 'Create Database',
   START_DATE = '20140101',
   EXPIRY_DATE = '20141231';
GO
CREATE LOGIN CreateDatabaseLogin 
FROM CERTIFICATE CreateDatabaseCert;
GO
DENY CONNECT SQL TO CreateDatabaseLogin;
GO
GRANT CREATE ANY DATABASE 
TO CreateDatabaseLogin;

We now need to copy the certificate to the database where the procedure will be run. There are a couple of ways to do this, but the most compatible is to read and write the certificate via a file. This next step writes the certificate:

BACKUP CERTIFICATE CreateDatabaseCert 
TO FILE = 'C:\Temp\CreateDatabase.cer'
WITH PRIVATE KEY 
(
    FILE = 'C:\Temp\CreateDatabase.pvk',
    ENCRYPTION BY PASSWORD = 'password',
    DECRYPTION BY PASSWORD = 'password'
);

Now we switch to the target database, restore the certificate, and delete the temporary files:

USE Sandpit;
GO
CREATE CERTIFICATE CreateDatabaseCert
FROM FILE = 'C:\Temp\CreateDatabase.cer'
WITH PRIVATE KEY
(
    FILE = 'C:\Temp\CreateDatabase.pvk',
    ENCRYPTION BY PASSWORD = 'password',
    DECRYPTION BY PASSWORD = 'password'
);
GO
--EXECUTE sys.sp_configure 'show advanced options', 1;
--RECONFIGURE;
--EXECUTE sys.sp_configure 'xp_cmdshell', 1;
--RECONFIGURE;
EXECUTE sys.xp_cmdshell 'ERASE C:\Temp\CreateDatabase.*';

Now create the procedure, and sign it using the certificate:

CREATE PROCEDURE dbo.CreateDatabase
(
    @DatabaseName SYSNAME
)
WITH EXECUTE AS CALLER
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @cmd NVARCHAR(max);
    IF COALESCE(@DatabaseName,'') <> ''
    BEGIN
        SET @cmd = 'CREATE DATABASE ' + QUOTENAME(@DatabaseName) + ';' 
        EXEC sys.sp_executesql @cmd;
    END
    ELSE
    BEGIN
        SET @cmd = 'INVALID DATABASE NAME';
        RAISERROR (@cmd, 0, 1);
    END
END;
GO
ADD SIGNATURE TO dbo.CreateDatabase
BY CERTIFICATE CreateDatabaseCert
WITH PASSWORD = 'password';

To test this works, we create a new login and user with permissions to execute the procedure only:

CREATE LOGIN test WITH PASSWORD = 'password';
CREATE USER test FROM LOGIN test;
GRANT EXECUTE ON dbo.CreateDatabase TO test;

The test itself:

EXECUTE AS LOGIN = 'test';
EXECUTE dbo.CreateDatabase @DatabaseName = 'NewDB';
REVERT;

The database is created successfully. To clean up, run the following:

DROP DATABASE NewDB;
DROP USER test;
DROP LOGIN test;
DROP PROCEDURE dbo.CreateDatabase;
DROP CERTIFICATE CreateDatabaseCert;
GO
USE master;
DROP LOGIN CreateDatabaseLogin;
DROP CERTIFICATE CreateDatabaseCert;

For more information on permissions, see Erland Sommarskog's excellent article.

Related Question