How to force users to specify a filegroup when creating tables/indexes

Question

I will soon be migrating to SQL Server 2017 and hence am working on beautifying some neglected aspects of our databases. One aspect is filegroups: my DBA predecessor has created multiple filegroups however as no one was really told about most objects just go to the default filegroup (PRIMARY).

Now I would like to enforce that people creating tables and indexes actually name the filegroup where their objects should reside. Meaning: A CREATE TABLE or CREATE INDEX without an "ON [Filegroup]" should roll back with an error that the filegroup is missing. Is there any way to do this an would you recommend proceeding with such an approach? I have just come across that policy based management does not work in preventing objects being created on the default filegroup.

Main point of doing this is that someboy should choose the filegroup for the objects in the first place instead of creating them in the default filegroup and afterwards have to move the objects based on rants by the DBA :-).

I thought about two techniques...however can't get started with either of them because of missing knowledge:

• Use database triggers to check if there is an on clause and if not roll back --> this seems to be quite fragile as it would depend on text analysis of the command text
• Make the primary filegroup really small in size (100KB?) in order for system tables still being able to fit in but other tables of moderate size would result in an error because there is not enough space left --> probably a bad idea as this could have severe side-effects on database availability and is not precise enough in preventing every user defined table or index to be created within the default filegroup

Thank you very much in advance for your help

Martin

Answer 1

You could possible create a new default filegroup with the Read_Only property set.

Add database file:

USE [StackExchange]
GO
ALTER DATABASE [StackExchange] ADD FILE ( NAME = N'StackExchange_DefRO', FILENAME = N'C:\SQL\SQL_DATA\StackExchangeRO.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [DEFAULTRO]
GO

Modify the new filegroup to READONLY:

USE [master]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'DEFAULTRO'
if(@readonly=0)
    ALTER DATABASE [StackExchange] MODIFY FILEGROUP [DEFAULTRO] READONLY
GO

Modify new filegroup to be DEFAULT:

GO
USE [StackExchange]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'DEFAULTRO') ALTER DATABASE [StackExchange] MODIFY FILEGROUP [DEFAULTRO] DEFAULT
GO

If a user now creates an object without specifying a filegroup:

USE [StackExchange]
GO

/****** Object:  Table [dbo].[NewTable]    Script Date: 21.12.2017 14:12:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[NewTable](
    [ID] [int] NULL,
    [Test] [nchar](10) NULL
)

GO

They will receive the error message:

Msg 1924, Level 16, State 2, Line 11
Filegroup 'DEFAULTRO' is read-only.