该错误发生在一个空数据库的时候
"Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the user 'TESTCCBDBUSER', because it does not exist or you do not have permission."
问题背景
加载CDXDBA.exe工具，选择c来创建一个空数据库
有如下环境数据
MS SQL Server Name: Logica7-CCBDB (测试环境中没有该实例名字)
管理员账户sa密码L0gica7数据库名称TESTCCBDB
校对SQL_LAtin1_General_CP1_CS_AS
目录 C:\temp 应用用户名TESTCCBDBUSER 密码 L0gica123
创建用户日志:运行正常，显示如下信息已经运行
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'
在更加周密的检测 sp_adduser 后发现如下信息
ALTER procedure [sys].[sp_adduser]
将上述变量链接至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).
检测 'L0gica123' 用户的属性信息显示默认模式是该用户，但是登录名称却是 'TESTCCBDBUSER'.
尝试安全设置
Selecting 'S', providing:
Name of the admin: sa
Password for 'sa': L0gica7
Database name: TESTCCBDB
ODBC Connection: CCB220
应用用户会连接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.
解决方案
在创建数据库的过程中如果用户名的id和密码是相同的，那么在运行的时候应该使用CD2202X1 exec
sp_addlogin 'AV2202X1','AV2202X1','CD2202X1' exec sp_adduser 'AV2202X1','AV2202X1', 'public' 这时会产生新登陆用户 授权给AV2202X1'
如果用户名密码不同且当你对安全域做了设置之后还无法工作
1> 2> 3> use CD2202X2 exec sp_addlogin 'AV2202X2','PASSWORD','CD2202X2' exec sp_adduser 'AV2202X2','PASSWORD', 'public'
New login created.
Granted database access to 'AV2202X2'.
为避免上述情况我们添加用户idAV2202X3到CD2202X2.
exec sp_adduser 'AV2202X3','AV2202X3', 'public'
exec sp_addlogin 'AV2202X3','PASSWORD','CD2202X2'
另外还要执行
1> 2> 3> use CD2202X2 exec sp_addlogin 'AV2202X2','PASSWORD','CD2202X2' exec sp_adduser 'AV2202X2','AV2202X2', 'public'
在数据库的安装文件夹Database Creation\files\Createusers.sql,将其中的
use __MSSQL_DATABASE__ exec sp_addlogin '__MSSQL_USER__','__MSSQL__USER__PWD','__MSSQL_DATABASE__' exec sp_adduser '__MSSQL_USER__','__MSSQL__USER__PWD', 'public'
改成
use __MSSQL_DATABASE__ exec sp_addlogin '__MSSQL_USER__','__MSSQL__USER__PWD','__MSSQL_DATABASE__' exec sp_adduser '__MSSQL_USER__','__MSSQL_USER__', 'public'
问题解决
当前位置：
论坛首页 »
自由讨论区 »
Hadoop，ERP及大数据讨论区
»
发起投票
技术讨论
加好友
发消息
2012-3-1 14:06:39 |
赞(
操作
