SQL Server principals – Logins & How does Login Authentication work in SQL Server?

[MSSQLFUN & Decode ITeS becomes a family of 1600 individuals across the globe. Please join us on
Facebook & YouTube.]

SQL Server Level Principals

SQL Server level principals are the only way to connect to SQL Server to access data. They are the entry guard to ensure only authorized person entries and perform legitimated functions only.

SQL Server Level Principals has 2 components.

• Logins
• Server Roles.

Logins

Login is part of the Server Scoped Security Principal used to connect and access the SQL Server. In other terms, Credentials like user-id password, Token / Certification based access to access your data.

SQL Server supports 3 types of logins out of 2 (SQL & Windows Login) is mostly used in the environment and available from legacy versions of SQL Servers.

· SQL Server authentication Login

· Windows authentication login

• Individual Windows user
• Windows group

· Azure Active Directory authentication login

• Individual AD user
• AD group

Windows Logins: Here, User Id and password stored in Active Directory (AD) and AD authenticates users before they connect to SQL. Microsoft preferred to use windows authentication to ensure updated password policies are applied with a series of encryptions. This is to ensure user credentials and data are not compromised. Windows logins are managed by domain admins and DBA cannot change the password or modify the user. Windows Logins are also called NT logins. Windows logins can be assigned to SQL server as:-

• Individual Login – Individual logins are normal user credentials got direct access to SQL Server by adding it in SQL Server Logins.

• Windows Group – Windows group is a collection of windows users. Windows group will be added in SQL Server logins and on basis of that user’s part of the group will get indirect access to SQL Server. There may be a possibility that a particular user is not part of the group directly but added in the further subgroups. That means. All Users of the Windows group and all subgroups in the hierarchy will get SQL access in one go. This is very useful when access needs to be given to a large group.

SQL Logins: SQL Logins use Server Authentication. SQL Logins created at SQL Server and stores usernames and passwords in the “Master” database server. There is not the role of the domain controller and active directory in the authentication. It can be used in situations where Active Directory is not available., but, whenever possible, use Windows Authentication exclusively. You can create multiple users under SQL authentication to provided different users different access as per their requirement. You can configure SQL Server to run on SQL Server and Windows Authentication called Mixed Mode.

How to Manage (Create \ Alter \ Delete) Login in SQL using SSMS (SQL Server Management Studio)?

Right click on Logins and click “New Login”
Select authentication type under which you want to create new login
For Windows Login, Enter Login Name with
For SQL Login, Enter Login Name with
For Windows Login, Option of password policy is greyed out because it’s managed by AD Default database – You need to select default DB for each login
For SQL Login, Option of password policy is enable, you can skip login from applying password policies. Default database – You need to select default DB for each login
Select Server role under which you want add login
Select Database and DB level permission to login. You can also assign it later.
To Delete, Right click on login and select delete
Delete Login Windows, Click OK. It will give you warning that DB users will remain orphan and you can delete it later.

How does Login Authentication work in SQL Server?

• Windows Login: Windows authentication is more secured than Database authentication because it works on encryption and certificate-based security procedure. Windows login pass token in place of a user ID and password to SQL Server. This Token is provided by windows active directory \ Domain controller or local computer. This token is authentication validity pass with SID details of logins. In case login is not part of direct SQL Server login, all associated windows groups & subgroups SID will be added to the token for validation. Once SQL Server received the valid token, it compares the SID from token to SID saved sys.server_principals system view in SQL Server. Based on the result of SID Comparison access of SQL Server is granted or denied.

• SQL Login: SQL Login authentication is quite simpler than windows login. SQL Login provided user ID & password to SQL Server. User ID & password is saved in the master DB system view. SQL Server compared the user ID and corresponding password. If user ID exists and the password is correct, access is granted.

How to check logins are SQL or Windows at SQL Server?

We can use the below query to review logins that exists at SQL Server are SQL or Windows.

Script to list permissions of all logins

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

Subscribe YouTube Channel Decode ITES

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles:-

http://social.msdn.microsoft.com/Profile/rohitgarg

https://www.linkedin.com/in/rohitgarg1986/