SQL server: Create user

Note

This page concerns the OnPremises option.
With the "Software as a Service" option, no server installations by the customer are necessary.

Note

OneOffixx can be run with Windows authentication as well as with SQL authentication. We recommend SQL authentication as it ensures a simpler setup in most cases.

An SQL user is required to access the data.

Create SQL user using SQL script

The following steps can be automated via the following SQL script.

The PASSWORD_HERE and possibly the corresponding login name must be exchanged, in the example oneoffixxuser.

CREATE LOGIN [oneoffixxuser] WITH PASSWORD='PASSWORD_HERE', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

EXEC sys.sp_addsrvrolemember @loginame = N'oneoffixxuser', @rolename = N'dbcreator'
GO

Create SQL user via UI

First, create a new login.

Make sure that "SQL Server Authentication" is selected and enter the password.

Caution

Depending on the password policy, the account password may expire. Make sure that the SQL user is always operational or disable "Enforce Password Policy" as well as "User must change password at next login".

Server roles / permissions

The SQL user or service account needs at least the "public" role to log in to the SQL Server as well as the "db_owner" role.

The OneOffixx server applications can create the required databases. This requires that the SQL user is "dbcreator". This role is optional. If you prefer the option without "dbcreator", you have to create empty databases yourself and assign the "db_owner" role to the SQL user or service account.

The dashboard allows to create new databases or copy existing ones. Therefore, it is recommended to keep the "dbcreator" role.