Existing SQL Server Database Configuration

Dave Whitby

Updated April 26, 2024 14:14

If you are using a standalone SQL environment and intend to introduce a new primary server, it is essential to create a new SQL instance to establish a connection. You can export the configurations from your current primary server and import them to the new primary server. However, please note that the migration history cannot be preserved.

The following instructions will allow you to create a user required if you specify 'Use Existing SQL Server' in the installer.

Users can be created two ways :

Step by step via the Microsoft SQL Server Management Studio
SQL Script

Step by step via the Microsoft SQL Server Management Studio

Make a note of following details as these will be required when running the installer and selecting 'Use Existing SQL Server'.

Server/SQL Instance name
Username and password
SQL Server Login Creation.

Connect to your SQL server instance using Microsoft SQL Server Management Studio.

Right Click on your server name > 'Properties' > 'Security' and enable 'SQL Server and Windows Authentication Mode'

Right Click on the 'Security' folder in the Object Explorer and then click 'New' > 'Login'.

Select 'General' under 'Select a page'

Populate 'Login name' with your chosen user name.

Select 'SQL Server authentication' and enter both 'Password' and 'Confirm password'.

Uncheck 'Enforce password expiration'.

Select 'Server Roles' under 'Select a page'

Check 'dbcreator'

Click 'OK'

It is recommended that above user be tested by creating a database as the user. This can be done by connecting to your SQL Instance with the username and password in step 2.

SQL Script

In addition to using SQL Server Management Studio, the following script can be executed. Note that you should set dbusername and dbpassword to the appropriate values.

 BEGIN TRY
  DECLARE @dbusername nvarchar(128)
  DECLARE @dbuserpassword nvarchar(128)
  DECLARE @sql nvarchar(4000)
  /*
  SET @dbusername = N'dbusername'
  SET @dbuserpassword = N'dbpassword'
  */

  IF NOT EXISTS (SELECT
      loginname
    FROM master.dbo.syslogins
    WHERE name = @dbusername)
  BEGIN
    SELECT
      @sql = 'USE [master]' + ' CREATE LOGIN [' + @dbusername + '] WITH PASSWORD=N''' + @dbuserpassword + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON' + ' ALTER SERVER ROLE [dbcreator] ADD MEMBER [' + @dbusername + ']'
    PRINT 'Creating Login : ' + @dbusername
    EXEC sp_executesql @sql
    PRINT 'Created Login: ' + @dbusername
  END
  ELSE
  BEGIN
    SELECT
      @sql = 'ALTER SERVER ROLE [dbcreator] ADD MEMBER [' + @dbusername + ']'
    PRINT 'Updating : ' + @dbusername
    EXEC sp_executesql @sql
    PRINT 'Updated : ' + @dbusername
  END

END TRY
BEGIN CATCH
  DECLARE @errormessage nvarchar(4000)
  SET @errormessage = (SELECT
    ERROR_MESSAGE() AS ErrorMessage)
  RAISERROR (@errormessage, 11, 0)
END CATCH