In SQL Server 2005, Microsoft introduced the new Schema object. This allowed us to de-couple the owner from the name of the object. You see, in previous releases the naming convention for a database object comprised four parts: Server.Database.Owner.Object. The new naming convention in 2005 and beyond, is now Server.Database.Schema.Object. This makes sense especially since Oracle has been using a Schema object forever but what happens during a SQL Server upgrade? Let’s see…
In SQL Server 2000, if Fred created a table called “Mytable” in the “Pubs” database on the “Usaserver”, the fully qualified name would be Usaserver.Pubs.Fred.Mytable. This assumes that Fred is not the database owner and that he has the permissions to create a table in the database. The problem would occur when Fred would leave the company or the department and we were faced with having to rename his objects and changing the code wherever those objects were referenced. This applied to Stored Procedures, Views and Functions too. The way we got around this limitation was that we would have a best practice that pointed to the database owner creating all database objects. That would define the owner as “dbo” as opposed to the actual owner name. Then all our objects in a database would have a standard “dbo” label for the owner. For instance, in the example above, if Fred was the owner of the Pubs database, the four-part name would be Usaserver.Pubs.dbo.Mytable. When Fred left the company, we could just assign a new database owner without having to change any code since the object names would remain the same. It would also guarantee an unbroken ownership chain which, as I have written in this blog before, is a good thing. But the downside of this strategy was that there was only one dbo label per database.
The new Schema object allows us to create distinct parts to a database and each schema can be owned by a different user. But it is still a best practice to have the database owner create all database objects including schemas because of the ownership chain feature. The schema can be given a meaningful name to describe the objects within it, for instance, HumanResources or Accounting etc. In this way, we can think of a schema as a container of database objects within a database. We can also apply security to a schema and therefore to all objects within the schema giving extra flexibility. The new feature of permission inheritance follows the new four-part naming convention: permissions are inherited from server to database to schema to object. This means we can now set Execute permissions once at the Database level without having to go into each Stored Procedure individually, as we used to do.
But what happens during an upgrade from SQL Server 2000 or SQL 7.0? Well, because backward compatibility is so important, the upgrade process will create a database user account AND a schema object with the same name for each database user account from the old release. In the above example, after the upgrade you would see a new schema object called Fred. This means that any application code, or references in stored procedures, functions or triggers would not need to be changed because the object name would remain the same: Usaserver.Pubs.Fred.Mytable. The question then is, should we proceed to rename our objects after an upgrade? The answer is, in a word – No. Renaming objects and putting them into new schemas involves searching through all code modules and making sure that the new name is specified. This is a lot of work, plenty of potential for error and has no immediate benefit. While the new Schema object is definitely a good thing, its benefits can be realized within your newly designed databases, just fine.
The general strategy after an upgrade is then: If it works, don’t fix it!
Cheers
Brian
Recent Posts:
Microsoft Training over the Internet
