To move from one SQL server to another, follow the instructions listed below.
Install and configure new SQL server per the Microsoft Installation instructions. (Note: Ensure that mixed authentication is enabled).
Step One: Creating the Intranet Connections SQL database
- Open the SQL Server Management Studio.
- Right click Databases and Select New Database...
- In the Database field name type in IntranetConnections
- Keep the rest of the servers default settings.
Step Two: Create the IntranetConnections Login
- Right click Logins (under security) and select new login.
- In the field name type in Intranet Connections
- Change the default windows authentication to SQL server authentication.
- Leave the password field blank.
- In the database field from the drop down box select IntranetConnections (see image below)
- 6. Now click on the User Mapping page
- Select the database IntranetConnections by clicking in the checkbox.
- Under Database roles make sure Public and db_owner are checked.
- Click OK to finish the login creation.
Step Three: Moving your old SQL content to your new SQL IntranetConnections database
- First create a backup of your old production IntranetConnections database. You can do this via the SQL Admin interface (i.e. Management Studio).
- Right click the database name > Tasks (or All tasks) > backup the database.
- Back it up to a file in a temp location.
- Now go to your Newly created IntranetConnections database, right click and select Restore > point to the location of your backed up database file.
- Restore the database (you may have to check the option to force a restore and change the restore database files if they are different).
- Once the database has been restored, go to the properties of the of the SQL IntranetConnections LOGIN and ensure that it has DB_Owner rights as described above.
- Right-click the database and click 'New Query'. Execute the following SQL statement:
exec sp_change_users_login 'auto_fix','intranetconnections';
Step Four: Create/Modify your IntranetConnections SQL Datasource in Coldfusion