How do I manually recreate the VPC_User account in SQL? (NETIQKB71740)

  • 7771740
  • 21-Aug-2009
  • 21-Aug-2009

Environment

VigilEnt Policy Center 5.x

Situation

How do I automatically recreate the VPC_User account in SQL?
VPC database has been migrated from one SQL server to another.

Resolution

The following SQL query will delete the old account, and re-create a new VPC_user account for you.  Please note that this query assumes that the name of the account being used is "VPC_User", which is the account name created by default during the installation of VigilEnt Policy Center:

USE [NetIQVPC]

GO
ALTER AUTHORIZATION ON SCHEMA::[vpc_user] TO [dbo]
GO
DROP USER vpc_user
GO
CREATE LOGIN vpc_user
With PASSWORD = 'G@llant$pirit'
,Default_Database = NetIQVPC
,Default_Language = English
,Check_Expiration = Off
CREATE USER vpc_user FOR LOGIN vpc_user
WITH DEFAULT_SCHEMA = vpc_user;
EXEC sp_addrolemember N'db_owner', N'vpc_user'
GO
ALTER AUTHORIZATION ON SCHEMA::[vpc_user] TO [vpc_user]
GO
 
Please note that in this SQL query the vpc_user account was recreated with the password ? G@llant$pirit ?.  Since this will not be the password that was previously being used by VPC we will need to update the DB.Properties file on the VPC server with this change. 
On the VPC server navigate to:
 
C:\Program Files\NetIQ\VigilEnt Policy Center\server\shared\classes
 
Locate the DB.Properties file, and open this file with note pad for editing purposes.
 
Then the password hash has to be changed to properly represent the G@llant$pirit password we created in SQL.  The hash for this password is : 
 
2OlKyxi6Xx9yyMdt6NkKNA==
 
 
Save the file, and then restart the VigilEnt Policy Center Service.

Cause

When a database is restored to a different server it contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. This condition is known as having "orphaned users."  Logins are associated to users by the security identifier (SID) and this login is required for access to the SQL Server server.  Since the SIDs will not match after the database is restored, this user account must be recreated.

Additional Information

Formerly known as NETIQKB71740

Before executing any queries against your SQL database, please take care to have proper back ups available.