Clarity: How to Verify Database Settings using Microsoft SQL Server 2005.

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Description:

These queries will assist you in determining whether the SQL Server database has been setup properly for use with Clarity.

Solution:

This Query will give you all of the accounts where "View Server State" is enabled.

USE MASTER
SELECT name
FROM sys.[server_permissions] PER 
  INNER JOIN sys.[server_principals] PRIN
     ON PER.[grantee_principal_id] = PRIN.[principal_id]
WHERE PER.[permission_name] = 'VIEW SERVER STATE'
  AND (PER.[state] = 'G' OR PER.[state] = 'W') 
ORDER BY name 
GO

This Query will determine if all of the required settings for the Clarity DB are set properly.

SELECT
is_read_committed_snapshot_on,
 is_ansi_nulls_on,
 is_arithabort_on,
compatibility_level,
collation_name,
is_quoted_identifier_on
from sys.databases
where name = 'Database Name'

This query will give you environmental information about the SQL installation:

Use MASTER
 
SELECT
SERVERPROPERTY('MachineName') as Machine_Name,
SERVERPROPERTY('ServerName') as Server_Name,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition,
SERVERPROPERTY('Collation') as Collation,
SERVERPROPERTY('CollationID') as Collation_ID,
SERVERPROPERTY('SqlCharSet') as Characterset,
SERVERPROPERTY('SqlCharSetName') as Character_Set_Name,
SERVERPROPERTY('SqlSortOrder') as SQL_Sort_Order,
SERVERPROPERTY('SqlSortOrderName') as SQL_Sort_Order_Name;
GO

Keywords: CLARITYKB, installation, implementation, configuration.

Environment

Release:
Component: PPMENV