In previous versions of SQL Server we had what we called “owners” of objects. Nearly every time you talked about an object “owner” you were talking about dbo. Think about the default name of any user defined functions you created. They always got “dbo.” added to the beginning of their name by the engine when you created them. This is because the owner for that function was dbo.

Now we can take that idea of object owners and extend it.  When you want to create different owners for different objects, you can define a schema, then set the objects to be a member of that schema.  Then when you grant permissions, you can grant permissions to the schema, rather than to each object one at a time.  This way, the objects aren’t owned by a specific login, they can be shared across several logins or groups.

This functionality isn’t really new or earth shattering.  You can really consider this an additional way to set up your objects and control their use.  Imagine setting up schemas that contain HR data, and as a result, only HR users can insert and update those tables.  They could even restrict who can see SSN data via the schema.  Then you can tie in other systems, let’s say the sales department needed to know who had their passports in order to allow certain users to make sales to customers outside of the US.  Their schema could be granted access to select from a specific view that only provides information on the employee’s passport status.

Be on the lookout for schemas to become a larger concern, especially when you begin work in very large databases.  Schemas can help organize the tables into functional groups and help control access.  Expect to find this happening more and more and you’ll be ready for it!

If you have any thoughts on using schemas, let me know!  I’d like to know if any of you have begun seeing them in your office.

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

1 comment

Leave a comment

Your email address will not be published. Required fields are marked *