Users and schemas on SQL Server 2005
Users and schemas on SQL Server 2005
|
| Release 9.3 |
   |
Changes in the way SQL Server 2005 and later releases implement schemas must be taken into account if you move from using SQL Server 2000 to using SQL Server 2005 or 2008. The first section below compares the difference in schema definition between SQL Server 2000 and subsequent SQL Server releases. The second section discusses how this impacts your ArcSDE implementation.
Separation between the users and schemas
In SQL Server 2000, although the concept of schema existed, it was synonymous with the owner. As far as the database administrator was concerned, the schema and the owner were the same. Therefore, all the tables, views, functions, and stored procedures had the following naming convention:
DatabaseName.owner.ObjectName
For example, objects named landbase.gis.parcels and landbase.gis.rivers reside in a database called landbase and are owned by a user called GIS.
This configuration in SQL Server 2000 meant
- Every object was tied to the database user who created it.
- Different owners could create objects with the same name. For example, landbase.gis.streets and landbase.tom.streets can exist in the same database.
- The user could not be deleted or disabled as long as it owned database objects.
For these reasons, the schema definition changed in SQL Server 2005; it is now compliant with the American National Standards Institute (ANSI) (SQL-99) Standard definition. Every object name consists of four parts: server.database.schema.object. The schema owner can be a user, a database role, or a Windows group.
It may be best to view the schemas in SQL Server 2005 as logical containers within each of the databases with the following characteristics and behavior:
- A database can have multiple schemas.
- Each schema must be owned by one user, group, or role. The ownership of a schema can be changed.
- Users can have a default schema in which they can create their objects. If objects are not qualified with a schema name, they will be created on the user's default schema. A default schema can be changed.
- The default schema can be assigned to multiple users.
How does this impact your geodatabase?
The user creating a feature class must have the same name as the schema to which the object is written. Much of the geodatabase security model is based on specific rights conferred on the owner of an object—the owner being the user that created the object. The owner or schema name must match the name of the user performing tasks such as dataset creation, dataset schema changes (for example, adding a new field), modifying permission, or deleting a dataset.
For example, a database named GIS containing a schema named landbase must have a user named landbase to enable feature class creation on the landbase schema. The feature class name in ArcCatalog will be GIS.landbase.<feature_class_name>, as it would in a SQL Server 2000 ArcSDE instance. However, landbase in SQL Server 2005 and 2008 refers to the schema rather than an owner. ArcGIS or ArcSDE will not allow an object to be created on a schema other than one matching the user's name.
That means you must create a schema for each user who will own data, the schema must have the same name as the user, and that schema must be specified as the default schema for the corresponding user. Schemas can be created using Management Studio by connecting to your SQL Server instance, expanding the Databases folder, expanding the database to which you want to add the schema, expanding the Security folder, right-clicking the Schema folder, and clicking New Schema.
NOTE: You cannot assign a default schema to a Windows group. Each object that is created by a member of a Windows group will automatically reside on a schema with the same name as that user.
