Cannot access restored backup - SQL Server Forums

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)

Cannot access restored backup

Author

Topic

steve keating
Starting Member

6 Posts

Posted - 2010-09-23 : 13:37:17

I have two SQL 2000 servers (both setup the same)that I am running Navision 3.6 on. I took a BackupExec backup of a database and restored it to the second sql server, not the one the backup was done on. When I use the Navision client to try to access the restored database, I cannot access the db unless the user's account has Systems Administrator Role checked in the Roles tab for the login in the Security folder. On the original server, no user account has the Systems Administrator Role checked. What do I need to do to fix this, or is there something I must do in the restor?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 13:39:41

Did you create the logins on the new server? Did you unorphan them with sp_change_users_login?

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

Subscribe to my blog

steve keating
Starting Member

6 Posts

Posted - 2010-09-23 : 15:51:31

No, I did not create the logins on the other machine, they were there already (I am not sure the passwords are the same). When I tried to login with the navision client I got the following error message: " 916 "HY024"[Microsoft][ODBC SQL Server Driver][SQL Server]Server user is not a valid user in database "xxx"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 16:04:43

Try logging in with the account via SSMS and let me know what you get.

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

Subscribe to my blog

steve keating
Starting Member

6 Posts

Posted - 2010-09-23 : 16:08:06

At this point I need to admit that I am new to working with SQL. The server I am working on is a test machine, so no problem if I screw up, but could you tell me where to go to do this, or where I can find step by step instructions. I've tried Googling this, but not sure where to go.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 16:28:18

Open up SQL Server Management Studio and try connecting to the server using the same credentials you are wanting to use in your ODBC driver.

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

Subscribe to my blog

steve keating
Starting Member

6 Posts

Posted - 2010-09-24 : 08:48:40

I have loaded SQL 2005 Server Management Studio Express. when I try to log in using the password that is associated with the backed up database, I get an error "LOgin failed for user XXX, (Microsoft SQL Server, Error:18456). If the solution is to run the sp_change_users_login, where do I do this in the SSMS?

steve keating
Starting Member

6 Posts

Posted - 2010-09-24 : 09:54:14

I have been able to perform the sp_change_users_login, and have tested it with two people. Thanks so much for your help. How do I close out this thread?

Kristen
Test

22859 Posts

Posted - 2010-09-24 : 10:22:59

I'm not much help with your original question, but "BackupExec backup of a database" always scares me; we get quite a few questions here from people trying to restore their DB from a backup made with some third-party database backup tool that goes direct from databases-to-tape etc.

my advice would be to use SQL Server's native commands to make a backup file, on disk, and then backup the disk file to tape like you do any other files. Side benefit is that the last backup file(s) will be available on disk, so if you need to restore you can do so without the hassle of finding the right tape (off site?

) and the delay of restoring from-tape (when a restore is needed its usually a time critical task with all sorts of people jumping up-and-down!)

Kristen
Test

22859 Posts

Posted - 2010-09-24 : 10:23:51

"How do I close out this thread?"

There isn't a system here on SQL Team, but some people edit the Subject to append "[Resolved]"

steve keating
Starting Member

6 Posts

Posted - 2010-09-24 : 10:46:34

Cannot access restored backup [Resolved]

Thanks so much for the help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-24 : 12:20:17

You're welcome, glad to help.

If you've many users to "unorphan", then you can use a custom script. Here's the one I use and wrote:

USE YourDbNameGoesHere
GO

DECLARE @SQL VARCHAR(100)

DECLARE curSQL CURSOR FOR
SELECT 'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + name + ''', ''' + name + ''''
FROM sysusers
WHERE issqluser = 1 AND name NOT IN ('INFORMATION_SCHEMA', 'sys', 'guest', 'dbo')

OPEN curSQL

FETCH curSQL INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@SQL)
FETCH curSQL INTO @SQL
END

CLOSE curSQL
DEALLOCATE curSQL
GO

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

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-09-24 : 12:26:46

I use this to generate a script which I then Edit / Run. It includes commented out code to drop users (instead of using the Sproc to sync the user), and steps to Create the login if it doesn't already exist (you'll need to supply a password).

Probably should be changed to be more "SQL2005+ Friendly" ... an exercise left for the reader


-- ===== Reinstate User Logins after Restore ===== --
-- List any User/Group profiles that need creating/reinstating
PRINT '-- *** Cut&Paste the following code and EXECUTE any appropriate snippets ***'
PRINT '-- *** Generated for database: ' + db_name() + ' ***'
SET NOCOUNT ON
SELECT	DISTINCT 
	CASE WHEN L.sid IS NOT NULL 
		THEN '-- User ' + COALESCE(U.[name], '[NULL]') + ' already exists on server'
		ELSE 'EXEC sp_addlogin @loginame=''' + U.name + ''', @passwd =''password'', @defdb=''' + db_name() + '''' 
			+ CHAR(9) + '-- Only add if required!!'
		END,
	CHAR(13)+CHAR(10)+'-- EXEC ' + db_name() 
	+ '.dbo.sp_dropuser @name_in_db=' 
	+ '''' + U.name + '''	-- Remove this user if access is no longer required to this DB',
	CHAR(13)+CHAR(10)+'EXEC ' + db_name() 
	+ '.dbo.sp_change_users_login @Action=''Update_One'', '
	+ '@UserNamePattern=''' + U.name + ''', '
	+ '@LoginName=''' + U.name + ''''
FROM	sysusers AS U
	LEFT OUTER JOIN
	(
		sysmembers AS M 
		INNER JOIN sysusers AS G 
			ON G.uid = M.groupuid
	) ON M.memberuid = U.uid
	LEFT OUTER JOIN master.sys.sql_logins AS L 
		ON L.[name] = U.[name] COLLATE SQL_Latin1_General_CP1_CI_AS
where	    U.islogin = 1 
	AND U.isaliased = 0 
	AND U.hasdbaccess = 1
	AND 
	(
		   G.issqlrole = 1 
		OR G.uid IS NULL
	)
	AND U.name NOT IN ('dbo')
-- ===== END OF Reinstate User Logins after Restore ===== --

Kristen
Test

22859 Posts

Posted - 2010-09-24 : 12:29:54

It basically generates one of two forms:

User does NOT already exist as a Login:


EXEC sp_addlogin @loginame='MyUserName', @passwd ='password', @defdb='MyDatabase'	-- Only add if required!!                                                                                                                                                                                                                                
-- EXEC MyDatabase.dbo.sp_dropuser @name_in_db='MyUserName'	-- Remove this user if access is no longer required to this DB                                                                                                                                                                                                                                
EXEC MyDatabase.dbo.sp_change_users_login @Action='Update_One', @UserNamePattern='MyUserName', @LoginName='MyUserName'

Or user does exist, and just needs re-syncing:


-- User MyUserName already exists on server                                                                                                                                                                                                                                                                                                           
-- EXEC MyDatabase.dbo.sp_dropuser @name_in_db='MyUserName'	-- Remove this user if access is no longer required to this DB                                                                                                                                                                                                                                        
EXEC MyDatabase.dbo.sp_change_users_login @Action='Update_One', @UserNamePattern='MyUserName', @LoginName='MyUserName'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources