Is it possible to limit the maximum disk usage for a user

Question

Is it possible to set a limit on the amount of physical disk space a user (or group of users) can use within a single database? For example is there some method of associating a schema with a filegroup and then setting a maximum size for the filegroup? The actual database would have other tables on it and would therefore want to have other filegroups which were unlimited.

Answer 1

If your users have create permissions in their own schema, then you can set up a DDL trigger to capture table creation that occurs on a different filegroup than the one you've associated with that user. You can't prevent the table from being created, but you can roll it back and return an error message.

First, let's add a filegroup and a file with a max size to an existing database:

USE [master];
GO

ALTER DATABASE floob ADD FILEGROUP SomeUser;
GO

ALTER DATABASE floob ADD FILE 
(
  NAME = SomeUser, 
  FILENAME = 'C:\...wherever...\floob_SomeUser.mdf',
  MAXSIZE = 20MB -- your quota, this part is important!
) TO FILEGROUP SomeUser; -- this part is also important!
GO

Now let's create a schema and a login that can control that schema:

CREATE SCHEMA SomeUser;
GO

CREATE LOGIN SomeUser WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO

USE floob;
GO

CREATE USER SomeUser FROM LOGIN [SomeUser] WITH DEFAULT_SCHEMA = SomeUser;
GO

GRANT CONTROL ON SCHEMA::SomeUser TO dbo;
GRANT CREATE TABLE TO SomeUser;
GO

First of all, with these settings, this user can't create tables in dbo or any other schema other than their own, which means by default they will only be able to create tables on their limited file[group]. If you connect to the database floob as this user and try to create a table in dbo:

CREATE TABLE dbo.foo(id INT);

You get this error:

Msg 2760, Level 16, State 1, Line 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.

And because of this user's default_schema, not specifying the SomeUser schema will still end up with a table created in SomeUser (though you really should be pushing for best practices here, especially in this case, and always specifying the schema name when creating or referencing objects).

Now we can create a trigger like this, which will roll back the entire batch in the case where a filegroup is not specified:

CREATE TRIGGER EnsureFilegroup
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
  DECLARE @e XML = EVENTDATA();

  IF @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)') = N'SomeUser'
     AND NOT EXISTS (SELECT 1 FROM sys.indexes AS i INNER JOIN sys.data_spaces AS d
       ON i.data_space_id = d.data_space_id
       WHERE d.name = N'SomeUser'
       AND i.[object_id] = OBJECT_ID(
        QUOTENAME(@e.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(128)'))
        + '.' + QUOTENAME(@e.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(128)')))
       AND i.index_id IN (0,1))

  BEGIN
    RAISERROR('You must place your objects on the SomeUser filegroup.', 11, 1);
    ROLLBACK TRANSACTION;
  END
END
GO

It is particularly handy to keep the user, schema and filegroup names the same, which will make it easier to have this as programmatic. You can first perform a check that the schema being used is not one of your "umlimited" schemas, then proceed with a check that compares the schema name to the filegroup name. If it doesn't match, this user has rights to the wrong schema and is trying to create objects in the wrong place, and an error will be raised:

ALTER TRIGGER EnsureFilegroup
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
  DECLARE @e XML = EVENTDATA();

  IF @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)') <> N'dbo'
    -- or NOT IN (N'dbo', N'other unlimited schemas')
  BEGIN

    IF NOT EXISTS (SELECT 1 FROM sys.indexes AS i INNER JOIN sys.data_spaces AS d
       ON i.data_space_id = d.data_space_id
       WHERE d.name = @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)')
       AND i.[object_id] = OBJECT_ID(
        QUOTENAME(@e.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(128)'))
        + '.' + QUOTENAME(@e.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(128)')))
       AND i.index_id IN (0,1))
    BEGIN
      RAISERROR('You must place your objects on your own filegroup.', 11, 1);
      ROLLBACK TRANSACTION;
    END
  END
END
GO