Login Rejected

Hello,I develop my application on two different workstations (one at work and one at home). On each, I created a database with the same name, and added identical users with the same login ID and password. For simplicity, let's just say the database is called "my_data", and the user is "my_user" with a password of "12345". Recently, for reasons beyond this post, I needed to copy the data from my work database to my home database. I tried both a Detach Database, and a Full Database Backup, and have gotten the following result: When I attach/restore the databases to my home computer, the login "my_user" and "12345" fails. I THINK that even though my databases have users with the same login ID and password in them, that the two users are actually DIFFERENT users because they were created on different servers. I tried to remove the user from the database, but the db server wouldn't let me since the user owned a schema (which I can't uncheck in user preferences because it is grayed out). So I think I need to figure out how to remove the user that is in the restored database, and re-add the user that exists on the server that the database was restored to. Any suggestions? How do I remove the owned schema from the user that is in the database? Thanks! Mike

Hi,

when you use a SQL Database User (not an active directory user), the SQL Server generates SID's for the users. In your case the SQL Server generates two different SID's for the user. You can try to use the following stored procedure to synchronise the SID's:

- sp_change_users_login 'report' -> Displays all missing users
- sp_change_users_login 'Auto_Fix', 'HERE_USER_NAME', NULL, ''

See the msdn site (http://msdn2.microsoft.com/en-us/library/aa259633(SQL.80).aspx) for more information.

Regards
Marc André

Login problem, mystery

Hi
I have installed MSDE ver 8.00.760.03 from the package sql2desksp03 located
at go.microsoft.com/fwlink/?linkid=13962
My intention is to develop an asp.net application with Microsoft Visual
Basic.Net Standard. Already installed. Because I can't create a database
with the
standard edition I want to use MS Access 2000 project to create the tables,
but when I try to create the project using the Microsoft SQL Server database
wizard and enter my SQL Server details as follows
name=svrname
user=sa
password=correctpassword
I get the following message.
Connection failed:
SQLState:'28000'
SQL Server Error: 18452
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'sa'.
Reason: Not associated with a trusted SQL Server connection.
If I leave the Microsoft SQL Server database wizard user and password boxes
empty I am able to create my project but unable to create tables.
I'm on WinXP SP2 with Administrative rights. MSDE is running and my password
is definately correct - Please help :-)
hi,
SimonW wrote:
> Hi
> I have installed MSDE ver 8.00.760.03 from the package sql2desksp03
> located at go.microsoft.com/fwlink/?linkid=13962
> My intention is to develop an asp.net application with Microsoft
> Visual Basic.Net Standard. Already installed. Because I can't create
> a database with the
> standard edition I want to use MS Access 2000 project to create the
> tables, but when I try to create the project using the Microsoft SQL
> Server database
> wizard and enter my SQL Server details as follows
> name=svrname
> user=sa
> password=correctpassword
> I get the following message.
> Connection failed:
> SQLState:'28000'
> SQL Server Error: 18452
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
> 'sa'. Reason: Not associated with a trusted SQL Server connection.
> If I leave the Microsoft SQL Server database wizard user and password
> boxes empty I am able to create my project but unable to create
> tables.
> I'm on WinXP SP2 with Administrative rights. MSDE is running and my
> password is definately correct - Please help :-)
MSDE installs by default only allowing trusted conections... you can modify
this behavior bot at install time, providing the
SECURITYMODE=SQL
parameter to the setup.exe boostrap installer or later, at runtime, hacking
the Windows registry as described in
http://support.microsoft.com/default...n-us;285097... stop and
restart the server..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very much Andrea :-)
"Andrea Montanari" wrote:

> hi,
> SimonW wrote:
> MSDE installs by default only allowing trusted conections... you can modify
> this behavior bot at install time, providing the
> SECURITYMODE=SQL
> parameter to the setup.exe boostrap installer or later, at runtime, hacking
> the Windows registry as described in
> http://support.microsoft.com/default...n-us;285097... stop and
> restart the server..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>