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.
Azure B2B Big Data Business Intelligence Conferences CTP Database Design Developer Fun facts GUI How-To Hyper-V Interview MCSA MCSE Mobile Reporting NoSQL O365 PASS PD Presentation Programming Publication RLS SAGO SBCS Security SoftwareONE SolidQ SolidQ Journal SP1 SQL Azure SQL Database SQL Database V12 SQL in the City SQL MVP SQL Server 2008 R2 SQL Server 2012 SQL Server 2014 SQL Server 2016 SQLSaturday SSMS SSRS St Lucia T-SQL T-SQL Tuesday TechEd 2013 Training TTSAC TTSSUG Upgrade VDI VHD Virtual Machines Win10 Windows 2012 Windows 2012 R2 XML
- ► 2013 (24)
- ► 2012 (42)
- ► 2011 (33)
- ► 2010 (21)