5

In Microsoft SQLServer 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?

8

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
  • 1
    But the question asked if it could be done with a stored procedure--so you need to have the trigger exec a stored procedure. :) – Tony Hinkle Jun 26 at 17:56
  • 1
    @TonyHinkle I see an "or" in there, so I'm gonna skip that part 😉 – Erik Darling Jun 26 at 18:31
  • @ErikDarling I am not a developer. Curious is it the new way (using EVENTDATA) of writing triggers? Very elegant. – SqlWorldWide Jun 26 at 19:10
  • 1
    @SqlWorldWide It's XML, so not exactly elegant 😁 – Erik Darling Jun 26 at 19:12

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.