Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Object Grouping Best Practices: Using Schemas for Logical Separation

Originally written in French last year, now revised for you in my native language below:

You've probably all noticed a change since SQL Server 2000 with respect to objects: formerly DB.ObjectOwner.Object , now DB.Schema.Object since 2005.   With Schemas, we can benefit from the grouping of the objects to make sense of our often complicated relational database management systems.  For sure, a user can still create an object within the database as UserName.Object but that is a little messy to clean up when that person leaves the company. Sp_rename can be used to fix those pesky messes however, if you have to deal with them.  

 Madrid's Domestic Airport, Spain

Examples:

Grant select, insert, update, execute on Schema::SchemaName to [JoBlow]
-- this is assuming that you have used a specific user JoBlow generated and SchemaName
-- if you are using the built-in roles, then see below

CREATE USER [Domain\UserName] FOR LOGIN [Domain\UserName] WITH DEFAULT_SCHEMA= [db_datareader]

 -- if you want a user to have read access, make their default schema db_datareader and not DBO

-- if you give them DBO, then they have database owner rights (!) and can control all roles and schemas

USE [DatabaseName]

-- if you have to elevate their rights, then use the db_datawriter role

 GO

EXEC sp_droprolemember N'db_datareader',

N'Domain\UserName'

GO

USE [DatabaseName]

 GO

EXEC sp_addrolemember N'db_datawriter',

N'Domain\UserName'

 GO 

When we create an object, we can simply associate it within a schema, which in turn makes managing the security on that group of objects easier.  The other advantages are that we can place a schema within the database by the name of the application that uses it, perhaps if multiple applications depend on a single database, making it easy to understand which objects are used by what/whom/how.

As you can see in the above example, it's easier to manage the security on the group of objects too, since you would set the permissions on schema level and not for each individual object - noticeably tedious beforehand.  Giving DBO (database owner schema which still exists) access to users is not the best solution to handle security, if you wish to maintain the principle of least privilege.  A preferred workaround for elevated privileges access would be by means of EXECUTE AS instead (great current article on that here).

 

For users of RedGate's SQL Compare 8 (divided up into Schema or Data Compare previously), the use of schemas is particularily useful from the point of view of a DBA trying to keep his objects in synch between the various stages of development (e.g. copy of production into Test environment, followed by a merge with development DB, to approve changes).  If the grouping of the objects worked on is based on schemas, then the turnaround time in development will be faster (assuming the use of a catch-all database for several applications), since there is the option to compare by schema only using the SQL Compare tool. Similarly, O'Shea gives a full-on real-world use of Schema Comparison and Snapshots here.

References:
· http://www.sqljunkies.com/WebLog/outerjoin/archive/2006/02/24/17635.aspx
·
http://www.sqlteam.com/article/understanding-the-difference-between-owners-and-schemas-in-sql-server

 

Comments

Posted by Anonymous on 6 July 2009

Pingback from  Dew Drop (Early Edition) – July 7, 2009 | Alvin Ashcraft's Morning Dew

Posted by Anonymous on 7 July 2009

Daily tech links for .net and related technologies - July 5-8, 2009 Web Development The MVC in JavaScriptMVC

Posted by PaulHunter on 14 July 2009

I've been using schema's for the reasons you listed.  I have them defined at the functional department level because they generally have the same access needs regardless of application.  They also help guard against SQL injection since; 1) users only access thru stored procedures, 2) nothing is owned by dbo (not even the schemas).

Posted by Hugo Shebbeare on 14 July 2009

Thank you Paul - I should beef up the security benefits, very true:)

Posted by Alocyte on 3 November 2010

I find that the built-in functionality for dbo schema objects fail on user-schema objects.

As am example - highlighting a table-name of dbo schema and pressing alt-F1 breaks down a useful report; while it can't find the schema object, and fails to run when including the schema.

Leave a Comment

Please register or log in to leave a comment.