Sql-server – Grant dbcreator only for databases matching prefix

permissionssql serversql-server-2017t-sqltrigger

In Microsoft SQL Server 2017+ I would like to grant the dbcreator role on a single user but only allow her to create databases whose name matches a fixed prefix.

Is it possible to do this at the database level using a built-in feature or a stored procedure?

Best Answer

You can use a server level trigger like this:

CREATE OR ALTER TRIGGER [database_name_check] ON ALL SERVER
      FOR CREATE_DATABASE
AS
      BEGIN
            SET NOCOUNT ON; 

            DECLARE @event_data XML; 
            SET @event_data = EVENTDATA();
            IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)') ) =  'NADABRUTO\edarl'
                 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)') ) NOT LIKE  '%Stack%')
                        BEGIN
                            RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
                            ROLLBACK;
                        END

      END;

GO
ENABLE TRIGGER [database_name_check] ON ALL SERVER;
GO

Or at the database level like this:

USE StackOverflow2013
GO 

CREATE OR ALTER TRIGGER [database_name_check] ON DATABASE
      FOR CREATE_DATABASE
AS
      BEGIN
            SET NOCOUNT ON; 

            DECLARE @event_data XML; 
            SET @event_data = EVENTDATA();
            IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(255)') ) =  'NADABRUTO\edarl'
                 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(255)') ) NOT LIKE  '%Stack%')
                        BEGIN
                            RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
                            ROLLBACK;
                        END

      END;

GO

ENABLE TRIGGER [database_name_check] ON DATABASE;
GO
Related Question