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.

One comment
Zahra 29 Aug 2015 at 7:24 am

I am the same age as you and I have the same feelings as you smtioemes. But I am torn between this paradox of materialism and idealism. We live in a pragmatic and material society, yet at the same time we do have the ability to get out of it.It will have to be done against the background of societal norms and pressure. If you do leave your job, and do something else. I will root for you. For doing that, I am rooting for myself too.

Featured Articles

 Site Author

  • Thanks for visiting!