Implementing Schema for Security

Ever since SQL 2005, I have found schemas to be indispensable because of the security they offer. In previous versions of SQL Server, in order to restrict access, you had to do it by creating a new database. In many cases, this was the main factor in creating a new database.

Now we have Schemas. These schemas, allow for an added layer of decoupling between a user and the objects they can access or execute.

When I create a database now, I usually create a schema that only the web can access. I name this Schema: App.

So basically, anything in the app schema is available to the web. From there, any procedures withing the app schema can access any other schema that is created with the same owner (i.e. dbo). However it still stops the web from being able to access objects of other schemas.

This also allows you to have security on an entire schema rather than having it at the object level.

To implement this, you create a new database role, and in the securables section select “all objects of the types” » and select schemas, then select the permissons.