April 25, 2006 at 12:30 am
I'm about to change databasserver. Better performance with new hardware.
I have moved the databases, jobs, DTS-packages, users and logins from the old server to the new server. Databases are restored from previous backups and logins are copied with scripts (advanced copy options).
After the Logins are copied to the new server, there is a problem with Database Access for all SQL Server authentication logins. They don't have access to any of the databases. All Windows authentication logins are correct.
What to do?
How do I move from one databasserver to another with correct logins?
Environment:
Windows 2000 on the old server and Windows 2003 on the new server.
SQL Server 2000 with latest SPs.
April 25, 2006 at 2:03 am
You need to fix orphan users. There is a script on this site http://qa.sqlservercentral.com/scripts/viewscript.asp?scriptid=1605 or you can find a script on the Microsoft site somewhere.
Regards
Carolyn
April 25, 2006 at 7:43 am
April 26, 2006 at 9:33 am
If you only needed a one-time fix, then you're in good shape.
If you have to move databases between servers more often, say between test and production, it helps if you create logins on all databases with the same security id (SID) and password. This only applies to SQL logins, Windows logins are maintained by the domain.
This query will script out SQL logins with SID and password (encrypted) so they can be recreated exactly on another server. There are a lot of extra space, but it will still execute.
select
'if not exists(select null from master.dbo.syslogins where name = N''' + l.name + ''')
exec sp_addlogin @loginame = N''' + rtrim(l.name) + ''', @defdb = N''' + l.dbname + ''', @deflanguage = N'''
+ l.language + ''', @encryptopt = ''skip_encryption'', @passwd = ', x.password, ', @sid = ', l.sid
from syslogins l
inner join sysxlogins x on x.sid = l.sid
where l.isntname=0 and l.name not in (N'sa', N'distributor_admin')
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply