I want to prohibit the creation of database names, where there are spaces and dashes.

Ie I want that when the user tried to create a database, it was not created as a result.

I know what can be done through politics and the trigger. Any examples?

Through politics for some reason I did not succeed.

  • one
    Checked the same thing - it worked. I want to prohibit creating names of the database, where there are spaces and dashes - you can still trigger. - i-one
  • @ i-one, can I have an example of a trigger? - iluxa1810

1 answer 1

I want to prohibit the creation of database names, where there are spaces and dashes.

This can be achieved with the server level DDL trigger:

CREATE TRIGGER CheckDatabaseName ON ALL SERVER FOR CREATE_DATABASE AS BEGIN SET NOCOUNT ON; DECLARE @evt xml, @dbName sysname; SET @evt = eventdata(); SET @dbName = @evt.value('(/EVENT_INSTANCE/DatabaseName/text())[1]', 'sysname'); IF @dbName like N'%[ -]%' BEGIN RAISERROR('Incorrect database name.', 16, 1); ROLLBACK; RETURN; END END

This solution, however, has limited applicability, because you can create a database with a suitable name.

 CREATE DATABASE [DBName];

and then rename

 ALTER DATABASE [DBName] MODIFY NAME = [DB Name];

Prohibiting renaming is much harder.

It would be possible to assign a trigger to two events at once - FOR CREATE_DATABASE, ALTER_DATABASE . However, the eventdata for the ALTER_DATABASE event

 <EVENT_INSTANCE> <EventType>ALTER_DATABASE</EventType> <PostTime>2017-07-24T12:09:21.997</PostTime> <SPID>53</SPID> <ServerName>HOST</ServerName> <LoginName>LoginName</LoginName> <DatabaseName>DBName</DatabaseName> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>ALTER DATABASE [DBName] MODIFY NAME = [DB Name]</CommandText> </TSQLCommand> </EVENT_INSTANCE>

contains a new name for the database only in the command text, and not in a separate field. Parse the text of the command to pull out a new database name is not an easy task.