SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Schema Security

By Steve Jones,

How many of you grant access to schemas in your databases? I'm curious because I slowly am starting to see more and more people using schemas in their designs and not defaulting every object to "dbo". That was the default action for many developers for years, and it's still a habit of mine. Without SQL Prompt to add the schema to my queries, I'd still be producing code that followed that practice.

Schemas exist to allow some separation of different types of objects. In early versions of SQL Server, we didn't have schemas, but considered the third of four part naming to be the owner. In other RDBMSes, the use of a schema was to allow grouping and separation of different objects. This was also, and still is, a security boundary that helps ensure that authorized users can be easily limited to data access for certain objects.

This is also a nice organizational boundary for related objects that have the same schema, or even name. I have seen auditing or etl schemas that maintain the same named table that exists in a dbo schema, but is separated by placing this in a separate schema. I have even seen some archiving schemas that move data to a related table that exists in a different schema, making it easy for new administrators and developers to find (or remove) older data.

This week, let us know if you manage objects and security with schemas or if you stick to granting access at a database/object level the two more common ways of working with databases. If you have never used schemas and stuck with the defaults, perhaps you want to learn a bit more about how schemas work and the ways in which they may make your work easier.

Total article views: 41 | Views in the last 30 days: 41
Related Articles

Shared database and Separate schema

Hi, We have used 'Shared database and separate schema' approach under Multi-tenant database archit...


how to check the Schema Objects Details in Each Database using SP

Schema Objects History for all Databases


AUDIT_SCHEMA_OBJECT_ACCESS_EVENT Produces Failed and Successful Event

AUDIT_SCHEMA_OBJECT_ACCESS_EVENT Produces Failed and Successful Event for a failed event


Utilising Schemas

Schema or Separate db?


Stairway to SQL Server Security Level 5: Schemas and Security

In this stairway level you’ll learn how you can give principals access to groups of objects by assig...

friday poll