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.
migrated from stackoverflow.com Aug 28 '12 at 15:16
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:
Now let's create a schema and a login that can control that schema:
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:
You get this error:
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:
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: