DBA by Proxy

Administration

I keep tight control on all my SQL Server instances, including the development servers. I have a script that adds the developers’ Active Directory security group to each user database’s db_owner role and throws in a few miscellaneous permissions besides. Nobody gets added to any server roles. They still need me to create new logins or databases but that doesn’t happen often and the system has worked well for years.

One of our development managers came to me saying they were expecting a rapid turnover of contractors and the platform they’re working with requires a new database for each developer. They didn’t want to bother me with frequent requests so could they have rights to create databases on the development server please? I was going to push back, assuring them it was no trouble as it only took a few moments. Then I had an interesting idea: I could write a stored procedure that would create a database without requiring the caller to have elevated permissions. It sounded like so much fun I decided to do it.

Signing a stored procedure allows you to grant elevated permissions to it instead of to the caller executing it. In broad strokes, the process goes like this:

  1. Create a master key if it doesn’t already exist.
  2. Create a certificate.
  3. Create a certificate login and database user.
  4. Grant elevated permissions to the certificate user and/or login.
  5. Create the stored procedure.
  6. Sign the stored procedure with the certificate.
  7. Grant execute on the stored procedure to the caller.

Executing a signed stored procedure is like using EXECUTE AS to impersonate the certificate login/user before executing the procedure. Unlike EXECUTE AS, the caller doesn’t need elevated permissions, just EXECUTE permission on the module.

Let’s knock out steps 1 – 4. I put the certificate in master so I can use it with a SQL login. The database needs a master key before you can create any security objects:
 

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD= 'NotM1Birthday';
CREATE CERTIFICATE ProxyDBA WITH SUBJECT= N'DBA by Proxy';
CREATE LOGIN DBAbyProxy FROM CERTIFICATE ProxyDBA;
CREATE USER DBAbyProxy FROM LOGIN DBAbyProxy;   -- not needed in this particular example
ALTER SERVER ROLE sysadmin ADD MEMBER DBAbyProxy;

 

Now let’s get some work done. Our stored procedure needs to accept a parameter for the database name and generate dynamic SQL around it. It also needs to grant the developers’ AD group sufficient permissions in the new database.
 

CREATE PROCEDURE dbo.MakeMeADatabase (@db_name SYSNAME) AS

  -- create the new database
  EXECUTE (N'CREATE DATABASE [' + @db_name + N'];');

  -- change the database owner from the caller to a non-privileged account
  EXECUTE (N'ALTER AUTHORIZATION ON DATABASE::[' + @db_name + N'] TO DBOwner;');

  -- create the db user for the developers' group and give it carte blanche
  EXECUTE (N'USE [' + @db_name + N']; 
  CREATE USER [Network23\Floor13] FROM LOGIN [Network23\Floor13];
  ALTER ROLE db_owner ADD MEMBER [Network23\Floor13];');
GO
ADD SIGNATURE TO dbo.MakeMeADatabase BY CERTIFICATE ProxyDBA;
GRANT EXECUTE ON dbo.MakeMeADatabase TO [Network23\Floor13];

 

I omitted quite a bit in this example for brevity and clarity, like error handling. The parameter should be sanitized against SQL injection attacks and checked that it’s a permitted identifier.

The version I use has some added functionality. I specify the logical and physical file names because I don’t care for the defaults and it sends an email to me with the database name and the login name of the user who created it. I also take an initial full backup so the nightly incremental won’t fail.

So what’s to prevent someone from modifying the procedure to make themselves a sysadmin? The signature is dropped when the procedure is altered and the ADD SIGNATURE statement requires elevated permissions. The developers’ login doesn’t have the required permissions in the master database.

How much time has this saved me? None. Less than none. It’s been used less than a half-dozen times. But I had fun doing it and blogging about it, so it’s all good.