There is an old saying that states that knowledge is power. The ability to securely store and retrieve data is one of the pillars of the IT world. With SQL Server there are several security options to ensure that your data is stored safely.
SQL Server supports two security modes
- Windows Authentication
- Uses the credentials of the person logged into the Windows machine.
- SQL Server and Windows Authentication (Mixed Authentication)
- Uses both Windows Authentication and SQL Server username and password combinations
If you are using Mixed Authentication, please note that sa (which means system administrator) by default, does not contain passwords. The sa username is very powerful and gives the user the right to manipulate all databases on SQL Server. After the installation of SQL Server, please make sure that changing the sa password is at the top of your list of things to do.
If you are using Mixed Authentication it is good practice to create a username and password for applications that would restrict access to only the databases that the application uses.
There are two types of permissions in SQL Server
- Server Roles
- Database Roles