Home » Posts tagged 'dcode'
Tag Archives: dcode
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,
|
 |
For SQL 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.
| SELECT SP.NAME AS LOGIN,
SP.TYPE_DESC AS LOGIN_TYPE, SL.PASSWORD_HASH, SP.CREATE_DATE, SP.MODIFY_DATE, CASE WHEN SP.IS_DISABLED = 1 THEN ‘DISABLED’ ELSE ‘ENABLED’ END AS STATUS FROM SYS.SERVER_PRINCIPALS SP LEFT JOIN SYS.SQL_LOGINS SL ON SP.PRINCIPAL_ID = SL.PRINCIPAL_ID WHERE SP.TYPE NOT IN (‘G’, ‘R’) |
Script to list permissions of all logins
| SELECT sp.NAME AS LoginName,
sp.type_desc AS LoginType, sp.is_disabled As Is_Disable, sp.default_database_name AS DefaultDBName, SL.sysadmin AS SysAdmin, SL.securityadmin AS SecurityAdmin, SL.serveradmin AS ServerAdmin, SL.setupadmin AS SetupAdmin, SL.processadmin AS ProcessAdmin, SL.diskadmin AS DiskAdmin, SL.dbcreator AS DBCreator, SL.bulkadmin AS BulkAdmin FROM sys.server_principals sp JOIN master..syslogins SL ON sp.sid = SL.sid WHERE sp.type <> ‘R’ AND sp.NAME NOT LIKE ‘##%’ |
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
Subscribe YouTube Channel Decode ITES
http://www.facebook.com/mssqlfun
Other Linked Profiles:-
Principals, Securables, Permissions – SQL Server Access Components
[MSSQLFUN & Decode ITeS becomes a family of 1600 individuals across the globe. Please join us on
Facebook & YouTube.]
SQL Server Access Authentication
SQL Server Security is the primary DBA responsibility and required to secure guard your data. SQL Server database contains your users and business data and this needs security from unauthorized access. SQL Server Access Authentication is a crucial aspect of SQL Server Security.
SQL Server provides a list of mechanisms to provide access to the user. For New DBAs or other than DBAs, this process seems to be complex and confusing. These access controls help to provided exact access to each required user to ensure ZERO extra privilege or data access to anyone.
Components of SQL Server Access Authentication
SQL Server access authentication has 3 components:-
- Principals: Objects that can request SQL Server resources called Principals. Access to Principals is depended on the scope. Based on the scope, Principals can be divided into 2 types:-
- Server Level Principals
- Database Level Principals.
- Securable: Securables are Server or Database objects to which authentication can be provided. Like Table, Endpoints, etc. Securable are divided based on scope, Server Scoped, Database Scoped & Schema Scoped.
- Permissions: Permissions are giving control of securable to the principal. Principal can perform the required operation of securable as permissions assigned, Permissions at Server level managed via Logins and Server roles whereas at database-level permissions are managed using database user and roles.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
Subscribe YouTube Channel Decode ITES
http://www.facebook.com/mssqlfun
Other Linked Profiles:-
Introduction to Microsoft SQL Server Management Studio (SSMS)
[MSSQLFUN & Decode ITeS becomes a family of 1500 individuals across the globe. Please join us on
Facebook & YouTube.]
Microsoft SQL Server Management Studio IDE (Integrated Development Environment) is a powerful GUI tool with a long list of features and options. SSMS is very useful and user friendly for all users like DBA, Developers, Testers, or Students.
Top Action You can do using SSMS:-
- Connect to Local \ Remote SQL Server Database Engine \ Analysis Services \ SSIS (Integration Services) \ SSRS (Reporting Services) Instance
- Explore Connected Server Properties and Objects like Logins \ Linked Servers
- Explore Database list, Database properties & objects
- Open New Query \ Query Parsing \ Execution \ Results (Grid \ Text \ File) and view execution plans
- Activity Monitor
- Lunch other SQL Server tools like SQL Profiler \ DTA
- Manage SQL Server Agent and Jobs
- Connect \ Create \ Manage Registered Server
- Connect to Local \ Remote SQL Server Database Engine \ Analysis Services (SSAS) \ Integration Services (SSIS) \ Reporting Services (SSRS) Instances using SSMS
- When you lunch SQL Server Management Studio (SSMS), “Connect to Server” window pop up for server and credentials details by default for Database engine Server Type. In case, if don’t or you had closed or you want to connect other server types, You can open it from Object Explorer > Connect > Select Desired Server Type “Database Engine” as below.
- Enter Server Name: SSMS prompts you to enter the SQL instance name to connect. It can be a local or remote SQL instance. In a live environment, one SSMS can be used to monitor and manage remote SQL instance for the complete connecting environment.
- Select authentication Type and Enter User Details: After mentioning the SQL instance name, you need to select the authentication type. Windows authentication or SQL authentication. In windows, your currently used windows AD access will be used to connect or in SQL authentication, you will provide SQL login details. Once you fill all details, It will connect you with the mentioned instance.
Sample Connection:-
- Explore Connected Server Properties and Objects like Logins \ Linked Servers
- To explore Server properties, Right Click on Instance Name and Click Properties. It will open windows with the list of server-level configuration and properties. You can review and make the required changes. Some Major and mostly used configuration from server properties:-
| General Tab for Server Edition\ Server Version \ OS Version \ RAM \ Processors details |  |
| Min \ Max Memory Configuration in Memory Tab |  |
| SQL Server Authentication Mode in Security Tab |  |
| Database files and Backup default location in the Database Settings tab |  |
| MaxDop (Max Degree of Parallelism) and CTP (Cost Threshold for Parallelism) in advance tab |  |
- Server Objects like Logins \ Triggers \ Linked servers be browed and reviewed.
- Explore Database list, Database properties & objects
- To Explore a list of available databases and Database objects, You need to browse like below:-
- To review and Configure Database properties, Right Click on Database Name & go to properties. Some of the major database properties are:-
| General Tab for Database Owner, Creation Date, Total & Free Space, Last backup timestamp details |  |
| Files Tab for Files details and modifications |  |
| Options Tab for Checking and changing Collation, Recovery Model, Compatibility Level, etc. |  |
- Open New Query \ Query Parsing \ Execution \ Results (Grid \ Text \ File) and view execution plans
- Open New Query \ Query Parsing \ Execution \ Results (Grid \ Text \ File)
| Results in Grid |  |
| Results in Text |  |
| Results in File (You will prompt to select a location to save the file with execution results) |  |
- Intellisense: SSMS IntelliSense is an interesting feature, this provides runtime suggestions for object names and reduce query-writing time. Intellisense populates all objects in the buffer and shows then as an option.
- Estimated & Actual Execution plan of queries
- Lunch other SQL Server tools like SQL Profiler \ DTA
- Activity Monitor: Activity Monitor can be started by right click on instance name. It’s a GUI monitoring wizard for activities & sessions running on the server.
You can drill down multiple options on Activity Monitor Page for details of each segment.
List of features and option in SSSMS is countless, I suggest to download and install it to explore.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
Subscribe YouTube Channel Decode ITES
http://www.facebook.com/mssqlfun
Other Linked Profiles:-
SQL Server Sample Databases
[MSSQLFUN & Decode ITeS becomes a family of 1500 individuals across the globe. Please join us on
Facebook & YouTube.]
To start your learning of SQL Server and testing of new features, Microsoft provided some of ready to use sample databases. These are very useful for students and new learners.
Microsoft provides 2 sample databases which can be found at: https://docs.microsoft.com/en-us/sql/samples/sql-samples-where-are?view=sql-server-ver15
1) AdventureWorks: One of the oldest sample databases, Microsoft keeps updating it with new versions. Directly available on MS site for download.
2) Wide World Importers: New entry in sample databases and can be downloaded from Github.
When you click on the yellow highlighted link, It will redirect you to Github and you can download backup files from there.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
Subscribe YouTube Channel Decode ITES
http://www.facebook.com/mssqlfun
Other Linked Profiles:-
Download SQL Server Developer / Evaluation / Express Editions
[MSSQLFUN & Decote ITeS becomes a family of 1400 individuals across the globe. Please join us on
Facebook & YouTube.]
Today, I will take you to the steps of downloading Download SQL Server Developer / Evaluation / Express Editions. As discussed in my previous blog Understand SQL Server Editions and Components, SQL Server Developer / Evaluation / Express are free to use editions with fewer limitations and restrictions.
Step 1- Open Link (https://www.microsoft.com/en-au/sql-server/sql-server-downloads).
Step 2- You Browse to SQL Server 2019 Setup pages, it will give you the option of downloading Enterprise Evaluation, Developer, and Express edition.
You click on the desired option to download the setup file. We will download the Enterprise Evaluation Edition.
Step 3- Sign up to download SQL Server 2019 evaluation
Step 4- Download link is ready, please click
Step 5-
- Click the file downloaded from the last step, “SQL2019-SSEI-Eval.exe”. Click to install it.
- Chose the “Download Media” option to have offline setup.
Step 6-
- Select Language
- Choose CAB file to download
- Select the location of the download file
- Click Download
Step 7- These are the downloaded files and these are not ready to install setup. We need to unbox the setup file by giving exe.
Step 8- When you execute it, it will ask for the location to save setup files
Step 9- Setup files are ready to install.
We will use this setup to install SQL Server 2019 instance upcoming lesson.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
Subscribe YouTube Channel Decode ITES
Other Linked Profiles:-