Symantec Management Platform (Notification Server)

This paper will help clarify understanding of Microsoft SQL Server schemas and how they are used in Notification Server. Please note that the schema description portions of this article come from "Working with Schemas, in SQL Server 2005", by Adam Machanic, found on http://SearchSQLServer.com

Creating all of these databases helped address the table management problem; now there was no question about where to look for a specific type of data. But the multi-database solution created other problems. DB maintenance tasks and design headaches, such as back-up, maintenance plans, or cross-database ownership chaining, etc. for each database became a necessary headache.

SQL Server 2005, thanks to its implementation of an ANSI SQL feature called schemas, fixes many of those issues -- and it brings a few nice bonus features as well.

Creating and Using Schemas

If you would like to determine which schemas are in your environment, run the following Query analyzer query:

SELECT * FROM SYS.SCHEMAS

This will list the schemas active for this SQL instance.

A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement. For instance, you might create a schema for sales-related data by using the following T-SQL:

CREATE SCHEMA SALES
GO 

Once created, you can add objects, such as tables or stored procedures, to the schema. To do so, you must prefix them with the schema name:

 
CREATE TABLE SALES.SALESPEOPLE
(
  SALESPERSONID INT,
  SALESPERSONNAME VARCHAR(50)
)
GO

Just as you might expect, you can access an object by referencing it by schema name:

SELECT * FROM SALES.SALESPEOPLE
GO

In SQL 2000 and 2005, every object participates in a schema, even if you don't explicitly specify one. In SQL 2000, the user and schema were the same. This made each database user own their own respective schema. This also meant that a database user could not see a given default schema if that schema was different than the user name.

In SQL Server 2005, creating an object without specifying the schema will cause it to be created in your default schema. A default schema can be assigned to a user when the user is created, but if it is not specified, the dbo schema will be used. Since any given database user might belong to a different default schema, it's a good idea to always scope tables by schema name. Even if your table is in the dbo schema, you should use two-part naming, such as the following:

SELECT * FROM DBO.MYTABLE
GO

You can also transfer objects between schemas. That's done by using the TRANSFER option of ALTER SCHEMA. To transfer the SalesPeople table from the Sales schema into the dbo schema, the following T-SQL would be used:

ALTER SCHEMA DBO
TRANSFER SALES.SALESPEOPLE
GO

Database Organization

Schemas are truly great from an organizational point of view. The following screenshot is an image of some tables from the AdventureWorks sample database, as viewed in SQL Server Management Studio:

This cross-section of tables includes human resources-related data, data about people, and production information -- all logically separated by virtue of participating in separate schemas. These tables are all in the same database, so they can share references, filegroups, backup and maintenance schemas, or anything else that tables in the same database can do together. In other words, you get all of the benefits of splitting up the database and none of the problems that might have driven you to want to split it up in the first place.

Assigning Schema Permissions

Another great benefit of schemas is that you no longer need to worry about assigning users' rights to objects on a granular basis. Rather than create roles for every combination of users, or having to maintain permissions for groups of objects on a per-user basis, schemas allow you to assign rights in blocks. Once assigned schema-level permission, a user will have that right for any object in the schema.

Keep in mind that granting permissions on a schema is a bit different than granting permissions for many other objects. The following does not work:

GRANT SELECT ON SCHEMANAME TO USERNAME

Instead, you need to scope the grant and specify that it's for a schema:

GRANT SELECT ON SCHEMA::SCHEMANAME TO USERNAME 

Database Build Scripts

Finally, I'd like to touch upon a little-known feature that's actually been around in SQL Server for quite a while. You might be surprised to learn that the CREATE SCHEMA statement is not actually new to SQL Server; it was added to the product in SQL Server 2000, but it didn't have much functionality until now.

What it did have then and still has today is the ability to allow a user to create objects that reference each other, out of order. That can be tremendously useful for development teams that keep database objects scripted in a source-control system, broken up into one file per object. The problem occurs when building a database out of the objects, which begs the question: How do you know the order in which to concatenate the files? For example, assume you have the DDL (data definition language) for a table of Web site users and a table of logins for those users. If you concatenate the files in alphabetical order, you'll end up with the following T-SQL, which fails when run:

CREATE TABLE LOGINS 
(
  USERID INT NOT NULL 
    REFERENCES USERS (USERID),
  LOGINDATE DATETIME
)

CREATE TABLE USERS 
(
  USERID INT NOT NULL PRIMARY KEY
)

Reversing the order doesn't help either. How will you deal with the Products table, which contains products that are sold, and the Sales table, which details which products were sold to which customers?

Luckily, schemas solve this problem nicely. When creating a schema, you can specify additional DDLs to define the objects that will go in the schema -- and SQL Server takes care of the ordering. To create the Logins and Users' tables shown above, just put them into a schema together:

CREATE SCHEMA USERINFO
  CREATE TABLE LOGINS 
  (
    USERID INT NOT NULL 
      REFERENCES USERS (USERID),
    LOGINDATE DATETIME
  )

  CREATE TABLE USERS 
  (
    USERID INT NOT NULL PRIMARY KEY
  )
GO

Schemas and Notification Server - How do they relate?

Notification Server requires that it's database objects default to the "DBO" schema. In the past, (SQL 2000 and prior), this occurred by the "dbo" user generally by owning the database.

Many have confused the idea of owning the database with the "db_owner" role which is assigned to database users. Ownership of all SA-level logins are, by virtue of being an SA, able to login to all databases as the user (schema) "DBO". Additionally the owner of the database also logs in to the database it owns as "DBO". A SQL login can own a database even if they have no other rights outside of that database on the SQL server.

In SQL 2000 only the owner of the database and all SA level SQL logins use the "DBO" schema, as needed by Altiris.

In SQL 2005 by default, a user defaulted to the "dbo" schema without having to be either the owner of the database or a SA level SQL login. Databases that were upgraded to SQL 2005 from SQL 2000 made the default schema of the user, the schema that matched the users name. So how can we tell?

While logged into a database using the same login that Notification Server uses, you can know the database user by running the following command in Query Analyzer:

SELECT USER

This will return the database user you are logged in with. With SQL 2000 if it does not return "dbo", then your SQL login does not explicitly use the "DBO" schema.

The "DBO" user always creates its objects as "dbo" unless a specific schema is specified. Database users that have "dbo_owner" role rights will have the same permissions as the "DBO" user, but unless specified, they will always use their default schema when creating objects. In addition, unlike the "DBO" user, all other users in a database can be assigned multiple permissions, even overlapping permissions, with the more restrictive settings taking precedence. The "dbo" user has all rights to that database which cannot be restricted.

Issues within the Altiris solutions occur when SQL objects get created under a schema other than "DBO".

To know which objects are created in any other schema besides DBO, run the following SQL command against the Altiris database:

SELECT NAME,TYPE FROM SYSOBJECTS WHERE UID>4

If it returns any results then the objects listed are database objects that have the wrong schema ownership. These objects, when created, were created because the database login used by Altiris did not have the correct schema. This happens frequently during installation, NSSetup activities, and repairs. Frequently, the database login used will not be a user who has the correct privileges and permissions in the dbo schema.

To resolve this issue, all the database objects that are not using the "DBO" schema need to be switched to the "DBO" schema. This can be done either one at a time or by running the Attached SQL script "Delete duplicate sysobjects and change ownership.sql" which will remove duplicates and change all database objects to be owned by the "DBO" schema. To do fix one object, run the following command putting in the objects schema name and name separated by a ".".

SP_CHANGEOBJECTOWNER 'SCHEMA NAME.OBJECT NAME','DBO'

This is a system stored procedure that performs some additional security checks, but runs a procedure very similar to:

ALTER SCHEMA DBO
TRANSFER SALES.SALESPEOPLE
GO

This results in the Sales.SalesPeople object now appearing as dbo.Salespeople.

If the "DBO" schema already has an object by that name, then this procedure will fail; in which one of the versions will have to be deleted, or have its object altered to be a part of a different schema and name. Generally the one created last should be kept and changed to be owned by "DBO".

To prevent other objects from getting created under the wrong schema, the SQL login account used by Altiris needs to be switched to default to the "DBO" schema.

For SQL 2000:

1. Open Enterprise Manager
2. Open the Altiris database
3. Remove the user that has the same name as the SQL login used by Altiris.
4. Open Query Analyzer using an account that has SA Rights to the SQL server.
5. Switch to the Altiris database and execute the following command:

     SP_CHANGEDBOWNER 'SQL LOGIN NAME USED BY ALTIRIS'
     
     

For SQL 2005:

1. Open the SQL Server Management Studio
2. Open the Altiris database Schemas
3. Remove the schema that has the same name as the SQL login used by Altiris
4. Open the Altiris database users
5. Edit the user with that has the same name as the SQL login used by Altiris
6. Change this users default schema to "DBO" and save