Return
-
Title
Cannot install SQL PI when the database user does not have the db_owner role on the PI repository database -
Description
Can not enable PI When setup PI repository using a new database. The following error message can be seen in the error message dialog:"Failed to enable SQL PI monitoring extension for agent xxx: User does not have permission to alter database xxx, the database does not exist, or the database is not in a state that allows access checks"
The new repository database can be created successfully, but schema creation will fail. No tables or index can be found in the database.
-
Cause
The database user is used for monitoring SQL server instance and for PI repository at the same time. The grant permission script for monitoring SQL server instance adds the database user to all existing databases including the "model" database. That means that every new database created, will automatically include this user as a normal user instead of db_owner. While the DB user must have db_owner role for the new created PI repository database. -
Resolution
WORKAROUND
Manually grant the DB user "db_owner" role for the new created PI repository database.
Enable SQL PI by selecting the "Create a New PI Repository Database for SQL Server" option and using the same database name as the newly created database.
STATUS
This issue has been logged as known issue FOG-728.
-
Defect ID
FOG-728