Skip to main content
Version: Next

SQL Server Security

Microsoft SQL Server security can be tricky to configure correctly. This page covers the most relevant details for a Cortex deployment. It is not a comprehensive guide to SQL Server security - refer to Microsoft's documentation for anything beyond what is covered here.

How SQL Server Security Works​

SQL Server uses two levels of access control:

  • Logins - at the server level. These allow authentication to the SQL Server instance itself and can grant server-wide roles such as sysadmin.
  • Users - at the individual database level. These grant database-specific roles such as db_owner, db_datareader, and db_datawriter.

Logins are mapped to database users to grant access to specific databases.

We recommend two separate databases - one for Cortex's own data and one for your archive (source) data.

From there, choose one of the following two approaches:

Option 1 - Single login:

Create one login (for example, cortex) and map it to:

  • A new user in the Cortex database with the db_owner role
  • A new user in the source database with the db_datareader role only

Do not grant db_datawriter or db_owner on the source database.

Option 2 - Two logins:

Create two logins (for example, cortex and sourcero). Map them as follows:

  • cortex - mapped to a user in the Cortex database with db_owner
  • sourcero - mapped to a user in the source database with db_datareader only

Do not grant db_datawriter or db_owner on the source database.

Common Gotchas​

  • Database backups (BAK files) often include user records, but these users are not always mapped correctly to logins when the backup is restored - even if the names match existing logins. Rather than reusing these users, we recommend creating a new login and mapping it to a new user with the same name, then deleting any leftover unmapped users.

  • Windows authentication - if you are using Windows credentials for the SQL Server connection, those credentials still need to be added as logins in SQL Server and mapped to database users before they can access any databases.