Upgrade to VMFG 6.5.4 and SQL 2008

unknown

I just set up a new server, Server 2008, with SQLServer 2008 and Visual 6.5.4. This was upgraded form SQL2000 and VMFG 6.4.1. I have been able to copy over a valid database and been able to connect using SYSADM but none of the other users can connect. I am not showing any orphans and if I delete a user then recreate the user I can then connect. The problme with is that I then have to go thorugh everyone (60 user) and reset thier secruity setitng in Visual.

Any ideas were to look for this 19005 error?

Thanks

unknown

We get this situation every time we copy a database (like to our training instance) on SQL Server 2005. The logins (and security privileges) still exist but have been orphaned from the database. We eventually wrote the procedure at the end of this message to fix it--this can only be used with SQL server user accounts, it has no effect on Windows domain accounts if you are using those instead. Once the procedure is created simply run USE (new db name) followed by EXEC GP_FIX_ORPHAN_LOGINS from a sql manager query window. There might be slight changes for SQL Server 2008 but it should be pretty close.

The following link discusses the routine used in the procedure: msdn.microsoft.com/.../aa259633(SQL.80).aspx

/****** Object: StoredProcedure [dbo].[GP_FIX_ORPHAN_LOGINS] Script Date: 04/05/2010 10:25:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[GP_FIX_ORPHAN_LOGINS]
AS
DECLARE @USER_ID VARCHAR(25)
DECLARE LOGIN_CURSOR CURSOR FOR
SELECT sys.syslogins.NAME FROM sys.syslogins JOIN sys.sysusers
ON sys.syslogins.NAME = sys.sysusers.NAME
WHERE sys.syslogins.SID <> sys.sysusers.SID
OPEN LOGIN_CURSOR
FETCH NEXT FROM LOGIN_CURSOR INTO @USER_ID
@FETCH_STATUS <> -1
BEGIN
EXEC sp_change_users_login 'update_one', @USER_ID, @USER_ID
FETCH NEXT FROM LOGIN_CURSOR INTO @USER_ID
PRINT @USER_ID
END
CLOSE LOGIN_CURSOR
DEALLOCATE LOGIN_CURSOR