Solved

Connecting to a new SQL Server Question 2

Posted on 2014-04-29
2
266 Views
Last Modified: 2014-05-05
We replaced a server 2003 SQL 2005 32 bit with a server 2008 R2 SQL 2005 64 bit.  Programs (using ODBC) updated on the new server run fine from the workstations (mostly Win 7 64 bit).  The same ODBC definition is unable to see or connect to the new server from the workstations.  An MS Access ADP is unable to run from the workstations;  it runs fine on the new server.  I have checked the SQL client drivers, comm protocols, and permissions.
Please help.

The ODBC problem is Question 1 - separate.
0
Comment
Question by:BFOG
2 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 350 total points
ID: 40031007
How did you create the users on the new server? You need a script to align the IDs if you created the users from scratch, then restored that bases from backup.

I used this script against each database on the new server

-- AUTO_FIX All users in DB
set nocount on
go

if exists(select * from tempdb..sysobjects where id =
object_id('tempdb..#t_users'))
drop table #t_users

CREATE TABLE #t_users ( [name] sysname)

INSERT #t_users ( [name] )
SELECT [name] from sysusers where name <> 'dbo' order by name

declare @lc_name sysname

SET @lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @lc_name IS NOT NULL
BEGIN
  IF EXISTS (SELECT * FROM   MASTER..syslogins WHERE  [name] = @lc_name)
  BEGIN
    PRINT 'Fixing ' + @lc_name
    EXEC Sp_change_users_login 'AUTO_FIX' , @lc_name
  END
  ELSE
  PRINT '*** not fixing ' + @lc_name
     
  SET @lc_name = (SELECT Min([name]) FROM   #t_users WHERE  [name] > @lc_name)
END

go


Kelvin
0
 

Author Comment

by:BFOG
ID: 40035899
Thanks,  I'll review and give it a try.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how the fundamental information of how to create a table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now