![adventureworks2012 schema diagram adventureworks2012 schema diagram](https://www.researchgate.net/profile/Elzbieta-Malinowski/publication/226379576/figure/fig1/AS:406760146915328@1473990584267/An-extract-of-the-AdventureWorksDW-schema.png)
The dbo-Schema is essentially a relic from the pre-2005 world, which still exists due to backward compatibility reasons, and which is at the same time used as default schema for name resolution (also like before).Īt this point, let me quote a member of the Security Team: “the intent with separating Schema from Users was to increase security – to allow more controlled delegation, etc. This is why the Security Team for SQL Server 2005 completely revised the schema concept, with the aim to facilitate the delegation of rights.
![adventureworks2012 schema diagram adventureworks2012 schema diagram](https://i.pinimg.com/originals/8f/2a/13/8f2a139e7f1b3e4a34794246789bc196.png)
The person not paying attention to any of it would face the problem that he could not delete accounts of former developers from the server as there were still objects assigned to them and that were in the end firmly anchored in the application. – At least the causer would be obvious ? Developers were thus equipped with db_owner(!)-rights and instructed to always specify objects in all DDL commands with dbo.objectname, hence as “Two-Part Name.” If this was forgotten, the developer’s name would suddenly be in front of the objects, and cross-references would in most cases not work. The second approach was of course entirely impracticable, putting aside one-man-developments. Objects are in the schema under the developer’s name, e.g.: “Andreas.Shoppingcart”.All developers put all objects into the dbo-schema.Up to SQL Server 2000, users and schemas were independent of each other, and there were only 2 options: Particularly as the concept of the Schema-User separation was a relatively new thing back then. This database is generally supposed to demonstrate the new features of the SQL Server Versions since 2005, but the concepts are not always being developed according to best practices. Because, to relieve all autodidacts, ironically, the well-known “AdventureWorks” database is anything but exemplary when it comes to schema-design. With this – admittedly long due – blog post I hope to provide a good reference. I admit that the amount of information on this topic is not as extensive as on the regulars “Indexing” and “Performance.” Hardening Security is an effort perceived as rather annoying, and developers are rarely trained in such areas in order to make the important decisions right at the design stage. Unfortunately, however, I still see databases every week which only seem to know the “dbo” schema. In the same way that a developer/architect has to deal with business processes for the ER-diagram and later tabular design, one has to deal with database access processes for the schema design. As a matter of fact, it is not really that difficult. Since the release of SQL Server 2005, in fact more than 10 years ago, it is of particular importance to me to train users in correctly using schemas. Here, schema does not mean the database schema with its tabular design but rather the “database object schemas,” also described as Namespace. This article picks up a topic I have been teaching time and again in seminars, at conferences and in forums for many years: Schema-Design.