Re: ODBC login problem

which gave me the following error:
"Msg 15291, level 16, State 1, Procedure sp_change_users_login , line 108
Terminating this procedure. The Username 'XXXXX' is absent or invalid."

I would expect this error if the 'MyLogin' user does not exist in the target database. My understanding from your initial post is that the user already exists so only steps needed were to add the login and remap.

If the user does not exist in the database, simply run:

CREATE USER MyLogin;

This will create new user 'MyLogin' and map it to the existing login 'MyLogin' you previously created.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Calculator" <Calculator@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:C20F8BC2-18A8-4F83-9BAD-527100D5F585@xxxxxxxxxxxxxxxx
Thanks Dan

I followed your advice and ran the queries which ran well until I got to:

--remap existing database user to new or exisitng login
EXEC sp_change_users_login
@Action = 'Update_One',
@UserNamePattern = 'MyLogin',
@LoginName = 'MyLogin'

which gave me the following error:
"Msg 15291, level 16, State 1, Procedure sp_change_users_login , line 108
Terminating this procedure. The Username 'XXXXX' is absent or invalid."

The Username in sp_change_users_login is the same 'MyLogin' that I set up in
CREATE LOGIN MyLogin?

Thanks


"Dan Guzman" wrote:

> In setting up the ODBC DSN configuration, I use my old SQL Server
> Authentication login and password, which I get the following error
> message:
>
> "Login failed for user 'XXXXX'. The user is not associated with a > Trusted
> SQL Server connection".

First, make sure the target SQL 2005 server is configured to allow both
Windows and SQL authentication. The trusted connection message indicates
that you are attempting Windows authentication rather than SQL
authentication.

Also, logins are not restored when you restore a database so you'll need to
create the SQL login on the target server if it doesn't already exists.
You'll also need to execute sp_change_users_login to remap the existing
database user to the SQL login on the target server. Example below. See
the Books Online for details.

USE MyDatabase
GO

--create a new login
CREATE LOGIN MyLogin
WITH PASSWORD = 'asecrtpasw0rd',
DEFAULT_DATABASE = MyDatabase
GO

--remap existing database user to new or exisitng login
EXEC sp_change_users_login
@Action = 'Update_One',
@UserNamePattern = 'MyLogin',
@LoginName = 'MyLogin'


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Calculator" <Calculator@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F3252C86-D583-4B2A-AFC8-854E96DCCBCA@xxxxxxxxxxxxxxxx
> This is possibly related to my restoration of the MS SQL DB from MSSQL
> 2000
> to MSSQL 2005:
>
> I backed up a database from MSSQL 2000 to move to a new server running
> MSSQL
> 2005.
>
> The DB appeared to restore without a problem (although not yet > confirmed);
> however, my ODBC login is now not accepted?
>
> In setting up the ODBC DSN configuration, I use my old SQL Server
> Authentication login and password, which I get the following error
> message:
>
> "Login failed for user 'XXXXX'. The user is not associated with a > Trusted
> SQL Server connection".
>
> My ODBC login was what I used on my MSSQL 2000 setup and I presume the
> backup and restore would copy these Authentication securities across to
> the
> MSSQL 2005 setup?
>
> Please let me know what I have overlooked.
>
> Thanks


.

Relevant Pages

  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.programming)
  • Logging in irrespective of the database access settings
    ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)