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.
Altiris requires that it's AppID or Database Credentials default to the "DBO" schema of its own database by owning the database. Many confuse 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 into 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 all SA level SQL logins use the “DBO” schema as needed by Altiris. In SQL 2005 a user can be switched to default to a different schema without having to be either the owner of the database or a SA level SQL login.
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.
select name,type from sysobjects where uid>4
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 ".".
sp_changeobjectowner 'Schema Name.Object Name',
SQL 2005 / 2008:
Alter Schema dbo Transfer 'Schema Name.Object Name
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
- Open Enterprise Manager
- Open the Altiris database
- Remove the user that has the same name as the SQL login used by Altiris.
- Open Query Analyzer using an account that has SA Rights to the SQL server.
- Switch to the Altiris database and execute the following command:
sp_changedbowner 'SQL login Name used by Altiris'
For SQL 2005
- Open the SQL Server Management Studio
- Open the Altiris database Schemas
- Remove the schema that has the same name as the SQL login used by Altiris
- Open the Altiris database users
- Edit the user with that has the same name as the SQL login used by Altiris
- Change this users default schema to "DBO" and save
For SQL 2014
- Open the SQL Server Management Studio
- Open Security>Logins
- Edit the SQL login used by Altiris
- Go to User Mapping
- Change the Default Schema to dbo
- Make sure db_owner is checked