Connect to database failed after copyback

[Former Member]

Hello all,

We have made system refresh from erp production to erp quality,we have configured the system as standalone and and added the user master import transports and now while trying to import it is giving an error connect to database failed and saying table SVERS does not exists

Please help

Rohit

[Former Member · Accepted solution]

Hi Rohit,

Check owner of the table SVERS in SQL Manager.

After database copy from erp production (SID of the system for example PRO) to erp quality (SID QUA) you must change owner of all database tables for PRO to QUA.

In this case owner of table SVERS must be "qua.SVERS" which means that only user QUA has access for this table.

There is a simple SQL to do this:

--    @OLDNAME@    - old <SID> lower case (ex. ebd).
--    @NAME@       - new <SID> lower case (np. ebp).
--    @PASSWORD@   - password for '@NAME@'
--    @DB_SID@     - new <SID> upper case
--    @HOSTNAME@   - host
--    @PASSWORD1@  - password for user SAPMssXPUser

--    @SIDADM@     - DOMAIN\sidadm, the r3 system administrator OS login. PROD\prdadm for example
--                   NOTE: The case is important.  Should be all lowercase.
--    @SAPSERVICE@ - DOMAIN\SAPServiceSID, the r3 service OS login. PROD\SAPServicePRD for example 
--                   NOTE: Case is also important here, and it should be exactly as shown above.
--    

use master
EXEC sp_addlogin '@NAME@', '@PASSWORD@','@DB_SID@'
go
use @DB_SID@
EXEC sp_change_users_login 'Update_One','@NAME@','@NAME@'
EXEC sp_grantdbaccess '@NAME@'
EXEC sp_addrolemember 'db_owner', '@NAME@'
go
use master
EXEC sp_grantdbaccess '@NAME@'
EXEC sp_addrolemember 'db_owner', '@NAME@'
go
use msdb
EXEC sp_grantdbaccess '@NAME@'
EXEC sp_addrolemember 'db_owner', '@NAME@'
EXEC sp_addrolemember 'TargetServersRole', '@NAME@'
go
use model
EXEC sp_grantdbaccess '@NAME@'
EXEC sp_addrolemember 'db_owner', '@NAME@'
go
use tempdb
EXEC sp_grantdbaccess '@NAME@'
EXEC sp_addrolemember 'db_owner', '@NAME@'
go
use master
grant all on xp_cmdshell to @NAME@
go
EXEC sp_addsrvrolemember '@NAME@', 'serveradmin'
EXEC sp_addsrvrolemember '@NAME@', 'dbcreator'
EXEC sp_addsrvrolemember '@NAME@', 'bulkadmin'
go


use @DB_SID@
go
if object_id('sp_change_sapuser') is not null
	drop procedure sp_change_sapuser 
go
create procedure sp_change_sapuser @oldid sysname, @newid sysname
as begin

declare @oldid_uid	smallint
declare @newid_uid	smallint    
declare @object 	sysname
declare @object_full	nvarchar(999)

set @oldid_uid = user_id(@oldid)
set @newid_uid = user_id(@newid)

if @oldid_uid is not null and @newid_uid is not null
	begin
	declare object_cursor cursor local for 
		select name 
		from sysobjects 
		where xtype in ('U', 'V', 'P', 'D')
		and name not in ('syssegments','sysconstraints')
		and @oldid_uid = uid

	open object_cursor
	fetch next from object_cursor into @object
	while @@fetch_status=0
	begin
		set @object_full = user_name(@oldid_uid) + '.' + @object
		exec sp_changeobjectowner @object_full, @newid
		fetch next from object_cursor into @object
	end
	end
else
	if @oldid_uid is null
		begin
		print '*** old database user does not exist ***'
		end
	if @newid_uid is null
		begin
		print '*** new database user does not exist ***'
		end
end

go
exec sp_change_sapuser '@OLDNAME@', '@NAME@'
go

use master
go
EXEC sp_grantlogin   '@SIDADM@'
EXEC sp_grantlogin   '@SAPSERVICE@'
EXEC sp_defaultdb    '@SIDADM@','@DB_SID@'
EXEC sp_defaultdb    '@SAPSERVICE@','@DB_SID@'
go 
EXEC sp_addsrvrolemember '@SIDADM@',     'sysadmin'
EXEC sp_addsrvrolemember '@SAPSERVICE@', 'sysadmin'
go
EXEC xp_sqlagent_proxy_account 'SET', '@HOSTNAME@', 'SAPMssXPUser', N'@PASSWORD1@'
go

Regards

Marcin Gajewski

[Former Member]

Hi,

Did u followed system copy document, had u performed the post-system copy steps to make QA system inline with old QA system.

Regards...

[Former Member]

Hi,

Can you please tell whether your transport directory is shared or not?

After our QA refresh is never import user masters by keeping QA as stand-alone system.

What we normally do is we configure the TMS again.(not from Domain Controller)

Login to QA client 000 or 100 any client. Then goto STMS>Overview>Transport Routes> Click on button Adjust with controller. This will configure TMS. Now check from STMS->Overview>SAP System>Check>Connection test.

Also pls check SAP Note 351586 - TP: DBSL error 107, DB error 208 with Connect

Hope this helps.

Thanks,

Sushil

Edited by: Sushil Suryawanshi on Mar 10, 2009 10:05 PM

[markus_doehr2]

> We have made system refresh from erp production to erp quality,we have configured the system as standalone and and added the user master import transports and now while trying to import it is giving an error connect to database failed and saying table SVERS does not exists

I'm not sure I understand what you have done.

What do you mean with "added the user master import transport"?

Did you follow the system copy procedure?

Markus