Have you ever had to reinstall SQL and then attach .mdb files or databases to your freshly installed instance?

If your .mdb database has users defined when you attach it, even if you create the respective user logins, they will be orphaned.

The simple fix is to use the stored procedure sp_change_users_login.

This is an example of how I use this procedure:

sp_change_users_login 'AUTO_FIX', 'myuser'

Replace 'myuser' with the usernames specified for your database.

A good webpage explaining the relationship between logons and users can be found at: http://vyaskn.tripod.com/troubleshooting_orphan_users.htm

I have developed an application which uses SQL Server as a data store and was looking for a way to install it with my appliation in a seamless fashion. With a lot of trial and error and a couple of hours trawling the internet for a decent source for this kind of info, I found a very interesting page on Microsoft's website.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/EmSQLExCustApp.asp

Finally, I decided to use the command prompt to  launch the installer.

SQLEXPR.EXE -q /norebootchk /qb reboot=ReallySuppress addlocal=all instancename=SQLCatalog SCCCHECKLEVEL=IncompatibleComponents:1;MDAC25Version:0 ERRORREPORTING=1 SQLAUTOSTART=1 SAPWD=SQLPassword SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0

Then I adapted an NSIS script I had (see below). All you need to do then is call the function UpdateMSDE from your NSIS script.

; English
!define URL_MSDE "http://www.microsoft.com/downloads/info.aspx?na=46&p=8&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=57856CDD-DA9B-4AD0-9A8A-F193AE8410AD&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2ff%2fd%2f4%2ffd407f12-0845-4d51-b084-779860d57ba5%2fSQLEXPR.EXE&oRef=http%3a%2f%2fwww.microsoft.com%2fsql%2feditions%2fexpress%2fdefault.mspx"

Function TestMSDE

        ;MessageBox MB_OK "Checking $1   $2"
 ReadRegStr $0 HKLM \
        "$1" "$2"

        IfErrors SQLServerNotFound SQLServerFound

 SQLServerFound:
                ;MessageBox MB_OK "Checking $3   $4"
  ReadRegStr $5 HKLM  $3 $4
                IfErrors SPNotFound SPFound

 SPFound:
  ;Check the first digit of the version; must be 8
  StrCpy $0 $5
  StrCpy $1 $0 1
  StrCmp $1 "8" SQLServer2000Found SQLServerVersionError

 SQLServer2000Found:
                Push 1
  Goto ExitCheckMinSQLVersion

 SQLServerVersionError:
  ;MessageBox MB_OK|MB_ICONEXCLAMATION  "This product requires a minimum SQLServer version of 8; detected version $0. Setup will install SQLExpress 2005."
  Push 0
  Goto ExitCheckMinSQLVersion

 SQLServerNotFound:
  ;MessageBox MB_OK|MB_ICONEXCLAMATION  "SQLServer was not detected; this is required for installation. Setup will install SQLExpress 2005."
                Push 0
  Goto ExitCheckMinSQLVersion

 SPNotFound:
  ;MessageBox MB_OK|MB_ICONEXCLAMATION  "SQLServer version $0 was detected. SQLServer version 8 (or later) is required for installation. Setup will install SQLExpress 2005."
  Push 0
  Goto ExitCheckMinSQLVersion

 ExitCheckMinSQLVersion:
 
FunctionEnd

Function ExistsMSDE

   ; search for SQL server
   StrCpy $1 "SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion"
   StrCpy $2 "CurrentVersion"
   StrCpy $3 "SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion"
   StrCpy $4 "CSDVersion"
   Call TestMSDE
  
   pop $1
   IntCmp $1 1 Found
  
   ; search for SQLExpress 2005 instance
   StrCpy $1 "SOFTWARE\Microsoft\Microsoft SQL Server\$SQLCatalog\MSSQLServer\CurrentVersion"
   StrCpy $2 "CurrentVersion"
   StrCpy $3 "SOFTWARE\Microsoft\Microsoft SQL Server\$SQLCatalog\MSSQLServer\CurrentVersion"
   StrCpy $4 "CSDVersion"
   Call TestMSDE
  
   pop $1
   IntCmp $1 1 Found
  
   ;jump to not found which will then start the installer
   ;IfFileExists 'C:\MSDERelA\Setup.exe' NotFound
  
   ; let's try to download SQLExpress 2005
   MessageBox MB_YESNO|MB_ICONEXCLAMATION  "SQLExpress 2005 not found. Do you want me to download installer from ${URL_MSDE}?" IDYES true IDNO false

true:
   nsisdl::download /TIMEOUT=30000 "${URL_MSDE}" "$PLUGINSDIR\SQLEXPR.EXE"
   Pop $0
   StrCmp $0 "success" Downloaded
   MessageBox MB_OK "Download failed: $0"
  
false:
   MessageBox MB_OK "Failed to detect MSDE or SQLExpress on this machine. The installer will continue but the application will fail to start. You will have to manually install the database engine and run the database creation scripts."
   Goto NotFound
  
Downloaded:
   ; run installer
   ExecWait "$PLUGINSDIR\SQLEXPR.EXE -q /norebootchk /qb reboot=ReallySuppress addlocal=all instancename=$SQLCatalog SCCCHECKLEVEL=IncompatibleComponents:1;MDAC25Version:0 ERRORREPORTING=1 SQLAUTOSTART=1 SAPWD=$SQLPassword SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0"
   ;ExecWait 'net start mssql$$$SQLCatalog'
   Sleep 10000 ; wait for a 10 seconds
   Goto Found
  
NotFound:
   Push 0
  
   Goto Exit
  
Found:
   Push 1
  
Exit:
  
FunctionEnd

Function UpdateMSDE
   Call ExistsMSDE
   pop $1
  
   IntCmp $1 1 Found
 
Found: 
Exit:

FunctionEnd

Yes I know. The script needs a bit of cleaning. But I trust you can do it.

1. DBCC SHRINKFILE(mydbname_log, 1)
2. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
3. Quick and dirty: using sp_detach_db and sp_attach_db system stored procedures.
http://www.swynk.com/friends/krishnan/movedatabase.asp

WARNING: READ ON AND RUN ONLY IF YOU KNOW WHAT YOU ARE DOING.

sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE

Quick Options:
1. Restore from a backup copy.
2. Run the Rebuild Wizard (Rebuildm.exe).
3. Start sqlservr with the -T3607 option.
4. Force status reset by running (WARNING: THIS IS A DANGEROUS OPERATION USE ONLY IF YOU KNOW WHAT YOU ARE DOING) UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME'
5. Call Microsoft professional services.

Related Links:
http://support.microsoft.com/default.aspx?scid=kb;en-us;180500
http://support.microsoft.com/kb/165918/

WORKAROUND:
To work around this problem, perform the steps below. Note that the final step is critical.
1. Ensure that the device file is actually available.
2. Use the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online.If you have not already done so, create this procedure by executing the Instsupl.sql script, found in the Mssql\Install directory.
3. Use the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. If you have not already done so, create this procedure by executing the Instsupl.sql script, found in the Mssql\Install directory. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online.
4. Execute sp_resetstatus in the master database for the suspect database:

use master
go
exec sp_resetstatus msdb -- replace msdb with your database name

You will see the following output:

Prior to Update sysdatabases attempt for DBName='msdb', the mode=0
and status=328 (status suspect_bit=256). For DBName='msdb' in
sysdatabases, status bit 256 was forced Off and mode was forced to
0. WARNING: You MUST stop/restart SQL Server prior to accessing this
database!

5. Stop and restart SQL Server.
6. Verify that the database was recovered and is available.
7. Run DBCC NEWALLOC, DBCC TEXTALL, and DBCC CHECKDB.