Skip to main content
Version: Next

SQL Server Security Setup for Cortex

Microsoft SQL Server can be particularly tricky to set up, so we provide some information here about the various nuances. This is not a comprehensive overview of SQL Server security; consult Microsoft's documentation for more details.

Brief overview of Microsoft SQL Server Security​

SQL Server's security model consists of "logins" at the server level (that allow authentication to the server itself and grant server-wide privileges like "sysadmin") and "users" at the individual database level (that grant database-specific privileges like "db_owner", "db_datareader", and "db_datawriter"). Logins can be mapped to users or be granted a server role to grant access to individual databases.

We recommend two separate databases: one for your Cortex data (we'll refer to this database as "Cortex" throughout this page) and one for your archive data (we'll refer to this as "Cherwell").

We then recommend one of two scenarios:

  1. Create a single login (for example "cortex") and map it to a new user (also called "cortex") in the Cortex database with the "db_owner" role and another in the Cherwell database with the "db_datareader" role. (Do not give this user "db_datawriter" or "db_owner" to the Cherwell database.)
  2. Create two logins (e.g. one called "cortex" and another called "cherwellro"). Map the "cortex" login to a new user in the Cortex database with the "db_owner" role. Map the "cherwellro" login to a new user in the Cherwell database with the "db_datareader" role. (Do not give this user "db_datawriter" or "db_owner" to the Cherwell database.)

GOTCHAs​

  • Database backups (BAK files) often include users. However, these users don't always get mapped properly to logins when the backup is restored, even if the users have the same names as other users or existing logins. If you do want to use any of these existing users (which we don't recommend), it's easiest to map the login to the user rather than the other way around. Instead, we recommend that you create a new login or map an existing login to a new user with the same name (as the login) and delete any unused users.
  • If you're using Windows credentials for the SQL server connection, you still have to add them as logins in SQL Server. Of course, they also need to be mapped to database users to have any access to the individual databases.