Security: Understanding Server Security Principals

Due to a lack of blogging in November I decided to do something special in December, so this month shall be SQL Server Security month. Why security you ask? While I was writing about a topic, I realized that some basic security information may be necessary for someone new to SQL Server to understand the post so I decided to blog about the basic topics before I publish my almost completed topic. So to start the security month I will begin with Server Security Principals.

Security principals are entities such as individuals, groups and processes that request SQL Server resources. They are managed at three levels: Windows and SQL Server levels which have an instance wide scope and Database levels which have a scope within a specific database.

When accessing SQL Server a user must specify an authentication mode and credentials. Currently there are two modes for authenticating access to the database engine - Windows authentication and SQL Server authentication. When installing SQL Server 2000, 2005 and 2008 you must specify the Authentication Mode for the instance by choosing either Windows Authentication Mode or Mixed Mode Authentication. The Mix Mode Authentication allows both Windows authentication and SQL Server authentication. It should be noted that your Authentication Mode can be changed after installation via the security page on the server properties dialog box. Your change in mode is implemented after restarting the SQL Server instance.

There are also two types of logins that let you manage access to the instance - Windows login and SQL Server login. Windows logins are logins that are mapped to Windows user accounts which allows the authentication process to be done by the Windows OS. SQL Server logins are not mapped to Windows user accounts and are created and stored in SQL Server.

Windows Authentication allows server access to only authenticated Windows users, hence only windows login can gain access to the instance. On the other hand, Mix Mode Authentication allows both Windows login and SQL Server login access to the instance.

Basic CREATE LOGIN syntax
Windows Login :
     CREATE LOGIN [Domain\User] from Windows

SQL Server Login :
     CREATE LOGIN [username] with PASSWORD='[password]'

Check MSDN: Create login for more information about the various options that are available when creating logins

SQL Server also has a set of Server-Level roles (also known as fixed server roles) available to assist with the management of these logins. You can simply assign and manage privileges by adding logins as a member of a roles for example, a Windows login that is added as a member of sysadmin can perform any activity in the instance. Check MSDN: Server-Level Roles for more information about the various roles.