Errors like the following are seen in the Notification Server logs after trying to run PMImport (aka MetaData Import Task or Import Patch Data) or update Vendors and Software:

When viewing the Ownership of stored procedures or tables in the database, it is evident that they were not created with the DBO schema.  These can be created as [app id].storedprocedure rather than dbo.storedprocedure. If the Application Identity was DBO, then they would be created as [dbo].storedprocedure.

 

While logged into a database using the same login that the Notification Server uses, identify the database user by running “select user” which will return the database user that is logged in.  With SQL 2000 if it does not return "dbo" then the 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 own database user name for 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.

 

Issues within the Altiris solutions occur when SQL objects get created under a schema other than "DBO". This can be verified by running the following SQL command against the Altiris database; if it returns any results then the objects listed are database objects that have the wrong schema ownership were created because the database login used by Altiris did not have the correct schema.

 

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 2000 script "Delete duplicate sysobjects and change ownership.sql" which will remove duplicates and change all database objects to be owned by the "DBO" schema.   There is also a SQL 2005 version of this script.

To fix one object, run the following command putting in the objects schema name and object name separated by a ".". 

SQL 2000: sp_changeobjectowner 'Schema Name.Object Name','DBO'
 Ex: sp_changeobjectowner '<user>.spCreateKeyIndex','dbo'

SQL 2005 / 2008: Alter Schema dbo Transfer 'Schema Name.Object Name'
 Ex: Alter Schema dbo Transfer <user>.spCreateKeyIndex 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.  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