Sp_adduser For Empty SQL Server Database Install Does Not Create User Correctly CCB
(Doc ID 1207194.1)
Last updated on MARCH 26, 2019
Applies to:
Oracle Utilities Customer Care and Billing - Version 2.2.0.0.0 and laterInformation 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 '<DB USER NAME>', 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: <SERVER NAME> (No named instance on our test environment)
Name of the SQL Server admin account: <ADMIN USERNAME>
Password for '<ADMIN USERNAME>': <ADMIN PASSWORD>
Database name: <DATABASE NAME>
Collation: SQL_LAtin1_General_CP1_CS_AS
Directory: C:\temp (note no spaces)
Application user: <DB USER NAME>
Password for the application user: <DB USER PASSWORD>
Name of the SQL Server admin account: <ADMIN USERNAME>
Password for '<ADMIN USERNAME>': <ADMIN PASSWORD>
Database name: <DATABASE NAME>
Collation: SQL_LAtin1_General_CP1_CS_AS
Directory: C:\temp (note no spaces)
Application user: <DB USER NAME>
Password for the application user: <DB USER PASSWORD>
- CreateUsers.log: This runs successfully, and the 'CreateUsers.log' shows that the following has been run:
1> 2> 3> use <DATABASE NAME> exec sp_addlogin '<DB USER NAME>','<DB USER PASSWORD>','<DATABASE NAME>' exec sp_adduser
'<DB USER NAME>','<DB USER PASSWORD>', 'public'
'<DB USER NAME>','<DB USER PASSWORD>', 'public'
The last part of the above code runs:
exec sp_adduser '<DB USER NAME>','<DB USER PASSWORD>', '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 <DATABASE NAME> users list under 'security' within SQL Server Management Studio, shows '<DB USER PASSWORD>' as a user (this should be <DB USER NAME>).
- The security logins section does however show '<DB USER NAME>'.
- Examining the <DB USER NAME> properties shows the login name to be <DB USER NAME>, with the correct default database.
- The user mapping for this user shows, for the database '<DATABASE NAME>' the 'user' and 'default schema' to be <DB USER PASSWORD> (this surely cannot be correct).
- Examining the properties for the '<DB USER PASSWORD>' user as shown in Clean DB Install 2.jpg, shows the default schema to be <DB USER PASSWORD>, but the 'Login Name' to be '<DB USER NAME>'.
- Attempt to configure the security (while making no changes to the observed user details):
Selecting 'S', providing:
Name of the admin: <ADMIN USERNAME>
Password for '<ADMIN USERNAME>': <ADMIN PASSWORD>
Database name: <DATABASE NAME>
ODBC Connection: CCB220
Application user that will connect: <DB USER NAME> (as set up previously, and shown in the screenshots)
Name of the admin: <ADMIN USERNAME>
Password for '<ADMIN USERNAME>': <ADMIN PASSWORD>
Database name: <DATABASE NAME>
ODBC Connection: CCB220
Application user that will connect: <DB USER NAME> (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 '<DB USER NAME>').
--Generating security ...
GRANT SELECT,INSERT,UPDATE,DELETE ON C0_INSTALLATION to <DB USER NAME>;
--Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the user '<DB USER NAME>', because it does not exist or you do not have permission.
GRANT SELECT,INSERT,UPDATE,DELETE ON C0_INSTALLATION to <DB USER NAME>;
--Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the user '<DB USER NAME>', because it does not exist or you do not have permission.
-- Workaround:
- To fix this issue, navigate to the '<DB USER NAME>' user in the database (as shown before), and select 'Properties'.
- Select the 'USer Mapping' for this user, and highlight the '<DB USER PASSWORD>' user entry and '<DB USER PASSWORD>' schema entry.
- Replace this '<DB USER PASSWORD>' entry with the correct name '<DB USER NAME>', as this is the correct user name. The properties now show the correct name.
- Run the security script again.
Solution
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |
| References |