Login Failed After Using Backup Database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Author

Topic

DeniseGoodheart
Starting Member

12 Posts

Posted - 2009-04-27 : 17:29:55

Good Day:

I am creating a VB.NET WinForms application using VS.NET 2005 and SQL Sever 2000. The IT department moved the TestDB database to a new server. My problem is that none of the users can make a connection to the TestDB database. I can successfully log in using the default SA login. I can see all the users on the SQL Server Users panel, but the users cannot log in. If a user tries to log in from the VB.NET application; an error message reads: Login failed. I also tried running the Login scripts with no success. I tried copying the .mdf file from TestDB Server,1 but I get a message that says the database is in use, so this is not an option because it is always in use.

The TestDB was originally on Server1. All the users could successfully access the database TestDB on Server1. Now the database TestDB Server1 backup was loaded on to TestDB Server2. I can see all the users on the SQL Server Users panel for TestDB Server2, but the users cannot log in.

I use a DSNless connection string which means I do not have to install an ODBC on each users computer. I simply change the DSN connection string to point to Server2 TestDB instead of Sever 1 TestDB.

SQL Server security authentication is set to mixed mode, so this is not the problem.

I was able to successfully login after doing the following, but unfortunately it did not work for the other users.
sp_password @new = 'wings', @loginame = 'Denise'

Any Suggestions?

Denise

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-27 : 17:51:21

This is almost certainly caused by orphaned accounts. Have you tried running sp_change_users_login to fix them? There's a script here somewhere that'll loop through them to fix it too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

DeniseGoodheart
Starting Member

12 Posts

Posted - 2009-04-29 : 12:21:05

test

quote:
Originally posted by DeniseGoodheart

Good Day:

I am creating a VB.NET WinForms application using VS.NET 2005 and SQL Sever 2000. The IT department moved the TestDB database to a new server. My problem is that none of the users can make a connection to the TestDB database. I can successfully log in using the default SA login. I can see all the users on the SQL Server Users panel, but the users cannot log in. If a user tries to log in from the VB.NET application; an error message reads: Login failed. I also tried running the Login scripts with no success. I tried copying the .mdf file from TestDB Server,1 but I get a message that says the database is in use, so this is not an option because it is always in use.

The TestDB was originally on Server1. All the users could successfully access the database TestDB on Server1. Now the database TestDB Server1 backup was loaded on to TestDB Server2. I can see all the users on the SQL Server Users panel for TestDB Server2, but the users cannot log in.

I use a DSNless connection string which means I do not have to install an ODBC on each users computer. I simply change the DSN connection string to point to Server2 TestDB instead of Sever 1 TestDB.

SQL Server security authentication is set to mixed mode, so this is not the problem.

I was able to successfully login after doing the following, but unfortunately it did not work for the other users.
sp_password @new = 'wings', @loginame = 'Denise'

Any Suggestions?

Denise

DeniseGoodheart
Starting Member

12 Posts

Posted - 2009-04-29 : 12:21:58

test

quote:
Originally posted by tkizer

This is almost certainly caused by orphaned accounts. Have you tried running sp_change_users_login to fix them? There's a script here somewhere that'll loop through them to fix it too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

DeniseGoodheart
Starting Member

12 Posts

Posted - 2009-04-29 : 12:29:18

Hello,

I am still figuring out this site and this is why there are repeat postings; sorry. There is no delete post.

I thought I posted this yesterday, but I do not see it here, so I'll post it again. I tried what you recommended with no success. I wonder if it is hopeless at this point.

The following are all the things I tried with no success:

----In that case, you should be able to remap db user to sql login with sp_change_users_login.

--------------------------------------------------------
---use this script if you don't want to bother with double-checking your usernames.
----just run it under the database that you believe has orphans.

set nocount on
select 'sp_change_users_login ''update_one'', ''' + name + ''', ''' + name + '''' + char(10) + 'go' + char(10)from sysusers where name not like 'db_%' and name not like 'public' and name not like 'guest'

---------------------------------------------------------------------------------

--better yet... here is a script which will produce the sp_change_users_login code for all
--databases on the server.

use master
go
set nocount on
select 'use ' + name +char(10) + 'go ' + char(10) + 'print ''Database: ' + name + ''''
+ char(10)+ 'go ' + char(10) + 'select ''sp_change_users_login ''''update_one'''', '''' + name + '''','''' + name + '''' + char(10) + ''''go'''' from sysusers where name not like ''''db_%'''' and name not like ''''public'''' and name not like ''''guest''''
+ char(10) + ''''go''''' from sysdatabases
--------------------------------------------------------------------
---SQLTeam.com

sp_change_users_login does not allow modifications to dbo, INFORMATION_SCHEMA, or sys logins. Try adding this code to your sp_change_users_login proc:

/*
Script to fix orphans when moving db to another server.
Note: Auto_fix option will create a SQL login id if necessary.
Note: Update_one option will NOT create a SQL login.
Note: Report option reports on sql login vs user login mismatches.
*/
SET NOCOUNT ON

DECLARE @UserName nvarchar(128)
,@MissingUsers varchar(4000)
,@cmd varchar(1000)

-- Re-sync user ids to master database
SET @UserName = ''

WHILE @UserName IS NOT NULL
BEGIN
IF @UserName IS NOT NULL
BEGIN
SELECT @UserName = min(a.name)
FROM sysusers AS a
WHERE a.name > @UserName and IsSQLRole = 0
AND name NOT IN('dbo','guest', 'INFORMATION_SCHEMA', 'sys')

-- Add this check for domain accounts
-- They do not need synced with the master database
IF (SELECT CHARINDEX('\',@UserName) )= 0
BEGIN
SET @cmd = 'sp_change_users_login ''update_one'', '''
+ @UserName + ''', ''' + @UserName + ''''

EXEC(@cmd)
END

---I get errors when I run the script above:
-- Server: Msg 156, Level 15, State 1, Line 3
--Incorrect syntax near the keyword 'not'.
--Server: Msg 170, Level 15, State 1, Line 38
--Line 38: Incorrect syntax near 'END'.

----------------------------------------------------------------------------

--SQLTeam.com

/*************************************************************************************
This procedure should be created in the Master database. This procedure takes no
parameters. It will remap orphaned users in the current database to EXISTING logins
of the same name. This is usefull in the case a new database is created by restoring
a backup to a new database, or by attaching the datafiles to a new server.
*************************************************************************************/

IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_fixusers
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_fixusers >>>'
END

GO

CREATE PROCEDURE dbo.sp_fixusers

AS

BEGIN

DECLARE @username varchar(25)

DECLARE fixusers CURSOR
FOR

SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END

CLOSE fixusers
DEALLOCATE fixusers
END
go
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>'
go
---Output Message:
---<<< CREATED PROCEDURE dbo.sp_fixusers >>>
----------------------------------------------------------------------------------
sp_change_users_login 'report'
--Output
----No users display
---------------------------------------------------------------------

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-29 : 14:06:04

There is a delete post, just click the trash can icon.

What is the exact error message?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources