Sp_adduser For Empty SQL Server Database Install Does Not Create User Correctly CCB (Doc ID 1207194.1)

Last updated on OCTOBER 18, 2016

Applies to:

Oracle Utilities Customer Care and Billing - Version: 2.2.0 and later   [Release: 2.2 and later ]
Information in this document applies to any platform.

Goal

The following error is encountered after creating an empty database:

"Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the user 'TESTCCBDBUSER', because it does not exist or you do not have permission."

Problem explanation:
  • Launch the CDXDBA.exe tool, select C to create an empty database
  • Provide the following environment data:
MS SQL Server Name: Logica7-CCBDB (No named instance on our test environment)
Name of the SQL Server admin account: sa
Password for 'sa': L0gica7
Database name: TESTCCBDB
Collation: SQL_LAtin1_General_CP1_CS_AS
Directory: C:\temp (note no spaces)
Application user: TESTCCBDBUSER
Password for the application user: L0gica123
  • CreateUsers.log: This runs successfully, and the 'CreateUsers.log' shows that the following has been run:
1> 2> 3> use TESTCCBDB exec sp_addlogin 'TESTCCBDBUSER','L0gica123','TESTCCBDB' exec sp_adduser

'TESTCCBDBUSER','L0gica123', 'public'

The last part of the above code runs:
exec sp_adduser 'TESTCCBDBUSER','L0gica123', 'public'
  • Examining sp_adduser in more detail shows the following code at the top (sys.sp_adduser.sql):
ALTER procedure [sys].[sp_adduser]
  • Examining the TESTCCBDB users list under 'security' within SQL Server Management Studio, shows 'L0gica123' as a user (this should be TESTCCBDBUSER).
  • The security logins section does however show 'TESTCCBDBUSER'.
  • Examining the TESTCCBDBUSER properties shows the login name to be TESTCCBDBUSER, with the correct default database.
  • The user mapping for this user shows, for the database 'TESTCCBDB' the 'user' and 'default schema' to be L0gica123 (this surely cannot be correct).
  • Examining the properties for the 'L0gica123' user as shown in Clean DB Install 2.jpg, shows the default schema to be L0gica123, but the 'Login Name' to be 'TESTCCBDBUSER'.
  • Attempt to configure the security (while making no changes to the observed user details):
Selecting 'S', providing:
Name of the admin: sa
Password for 'sa': L0gica7
Database name: TESTCCBDB
ODBC Connection: CCB220
Application user that will connect: TESTCCBDBUSER (as set up previously, and shown in the screenshots)

This fails, providing the errors shown below.The errors are repeating, and following this pattern (Cannot find the user 'TESTCCBDBUSER').

--Generating security ...
GRANT SELECT,INSERT,UPDATE,DELETE ON C0_INSTALLATION to TESTCCBDBUSER;
--Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the user 'TESTCCBDBUSER', because it does not exist or you do not have permission.

-- Workaround:
  • To fix this issue, navigate to the 'TESTCCBDBUSER' user in the database (as shown before), and select 'Properties'.
  • Select the 'USer Mapping' for this user, and highlight the 'L0gica123' user entry and 'L0gica123' schema entry.
  • Replace this 'L0gica123' entry with the correct name 'TESTCCBDBUSER', as this is the correct user name. The properties now show the correct name.
  • Run the security script again.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms