Let us first see the fully QUALIFIED query name to access a table for SQL Server 2000 and SQL Server 2005.

SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]
SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]
SQL Server 2008: [DataBaseServer].[DataBaseName].[Schema].[Table]

In SQL Server 2000, prior to dropping the USER who owns database objects, all the objects belonging to that user either need to be dropped or their owner has to be changed. Every time a user is dropped or modified, system admin has to undergo this inconvenient process.

In SQL Server 2005 and the later versions, instead of accessing a database through database owner, it can be accessed through a schema. Users are assigned to schemas, and by USING this schema a user can access database objects. Multiple users can be assigned to a single schema, and they all can automatically receive the same permissions and credentials as the schema to which they are assigned. Because of the same reason in SQL Server 2005 and the later versions – when a user is dropped from database – there is no NEGATIVE effect on the database itself.

Let us first see the fully qualified query name to access a table for SQL Server 2000 and SQL Server 2005.

SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]
SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]
SQL Server 2008: [DataBaseServer].[DataBaseName].[Schema].[Table]

In SQL Server 2000, prior to dropping the user who owns database objects, all the objects belonging to that user either need to be dropped or their owner has to be changed. Every time a user is dropped or modified, system admin has to undergo this inconvenient process.

In SQL Server 2005 and the later versions, instead of accessing a database through database owner, it can be accessed through a schema. Users are assigned to schemas, and by using this schema a user can access database objects. Multiple users can be assigned to a single schema, and they all can automatically receive the same permissions and credentials as the schema to which they are assigned. Because of the same reason in SQL Server 2005 and the later versions – when a user is dropped from database – there is no negative effect on the database itself.