How to force users to specify a filegroup when creating tables/indexes
Martin Guth (imported from SE) 
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][1] in preventing objects being created on the default filegroup.

Main point of doing this is that somebody 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.


  [1]: https://www.brentozar.com/archive/2012/11/creating-objects-on-a-specific-filegroup-with-policy-based-management/
Top Answer
John K N (imported from SE) 
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.
comments
transcript

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.