Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Will these users be creating their own tables at will? –  Aaron Bertrand Aug 28 '12 at 13:00
    
Yes the users will be creating their own tables at will. –  SQLMIKE Aug 28 '12 at 14:05
add comment

migrated from stackoverflow.com Aug 28 '12 at 15:16

This question came from our site for professional and enthusiast programmers.

1 Answer

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
share|improve this answer
2  
Good stuff Aaron. Very nice. –  RBarryYoung Aug 28 '12 at 15:33
1  
Just love the Ensurefilegroup trigger –  Edward Dortland Aug 29 '12 at 6:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.