Basics of Database Administration in SQL Server: Part 3

SQL Server Logins

In this part we will try to understand each and every thing related to logins. So let's move ahead to explore SQL Server logins and if you missed Part 1 & Part 2 you can check them out on the following link:

• Basics of Database Administration in SQL Server : Part 1
• Basics of Database Administration in SQL Server: Part 2

Login
A login is simply a set of credentials to gain access to SQL Server that requires proper authentication.

Users need a login to connect to SQL Server or we can say logins are associated to a user and the association is done by a Security Identifier (SID). We can create a login based on a Windows Authentication (like a domain user or a Windows domain group) or we can create a login with SQL Server Authentication.


Figure 1: Illustration of SQL Server Login

You can refer to Abhishek Yadav's article on “Logins and Users in SQL Server “ for more information in which he explained very well about SQL Server logins and users.

Properties of logins

1. It is a server level entity.
   
   
   Figure 2: Demonstrating that login is a server level entity
   
   
2. It is a set of credentials, in other words username and password are required. Refer to Figure 1.
   
   
3. Login information is stored in the sys.syslogins/sys.server_principals table in the master database. In Figure 3, the output in my case is of 27 rows, I have taken only those rows that covered all the types of logins, for example Windows, SQL, certificate mapped and server role, so don't be confused here.
   
   
   Figure 3: Showing sys.server_principals output
   
   Note 1: Here I want to emphasize what the difference is between sys.syslogins & sys.server_principals and it is very important to know that:
   
   sys.syslogins: It is a compatibility view to support 2000 databases and hence not recommended for use from SQL Server 2005 onwards.
   
   This includes logins that are Windows, certificate mapped, or SQL authentication based.
   sys.server_principals: It is recommended to use this view from SQL Server 2005 onwards.
   Apart from Windows, certificate mapped or SQL authentication based logins it also includes server role information.
   
   Note 2: SQL Server logins enclosed in double hash signs (##) represent internal logins created from certificates. The installation process will create users like "##MS_PolicyEventProcessingLogin##", so do not be surprised when you see them on SQL Server. It is very clear from Figure 3.
   
   
4. Logins are associated with users by a Security Identifier (SID), in other words we can say if a database user exists but there is no login associated, then in this case the user will not able to log into SQL Server. We can check it by Stored Procedure sp_helplogins or sp_msloginmappings.
   
   
   Figure 4: Showing "XYZ\yashwant.kumar" associated with the user "yashwant" by SID "0x01050000000000051500000094F29E736A3CA94F37C329CD7B540000" using sp_helplogins in a query window and in the Object Explorer also.
   
   or
   
   
   Figure 5: Showing output of sp_msloginmappings that also shows that "XYZ\yashwant.kumar" login is associated with the user "yashwant".

Exploring More about Logins


Figure 6: Login Properties Page.

The Login Properties page is divided into the following five sections:

1. General
2. Server Roles
3. User Mapping
4. Securables
5. Status

1. General
When we open the General tab of the login properties page we can see the following information:

1. Login Name: Information about the name of the login including the authentication types information.
   
2. Password: It is a password for the login name.
   
3. Specify Old Password: If we want to change the password. The following is the procedure to change the password:
   
   
   1. Right-click on login test1 then click on properties.
   2. Delete the old password and enter a new password in the password box.
   3. Again enter the new password in the confirm password box.
   4. Enable the check box to specify the old password and input the old password.
   5. Click OK. It's done now. Login with new password and check that it's done.
      
      
      Figure 7: Demonstration of changing password of login
      
4. Enforce password policy: enable this checkbox, if you want to enforce the password policy.
   See more about password policy from the following link.
   
   
5. Enforce Password Expiration: enable this checkbox if you want a password expiration time for the login.
   
   
6. Mapped to Certificate: Certificates are the way to encrypt with a digitally signed object.
   The certificate provides database-level security control.
   
   We can execute sys.certificates views to see the certificates, for example:
   
   1. select name,certificate_id,principal_id, pvt_key_encryption_type_desc from   
   2. sys.certificates;  
   
   Figure 8: Querying sys.certificates
   
   
7. Mapped to Asymmetric Key: These are the keys in SQL Server for encrypting and decrypting data that is being transmitted from one place to another.
   
   We can see the asymmetric keys by querying the view for sys.asymmetric_keys, for example:
   
   1. select name,principal_id,pvt_key_encryption_type_desc,algorithm_desc from sys.asymmetric_keys  
   
   Figure 9: Querying sys.asymmetric_keys
   
   Note: SQL Server suports three algorithms for asymmetric key encryption: RSA_512, RSA_1024 & RSA_2048.
   
   
   • RSA is made of the initial letters of the surnames of Ron Rivest, Adi Shamir and Leonard Adleman, who first publicly described the algorithm in 1977.
     
     
   • All three RSA_512, RSA_1024 & RSA_2048 algorithms are all based on the RSA cryptosystem. The difference in these RSA is the key length: 512, 1024 or 2048 bits. The longer the key (the more bits it has) results in more security of the encrypted data is and more bits also means that more CPU resources will be used.
     
8. Map to Credential: A credential is a record that contains the authentication information required to connect to a resource outside SQL Server. ( Source: msdn.microsoft.com.)
   
   Note: A single credential can be mapped to multiple SQL Server logins. However, a SQL Server login can be mapped to only one credential.
   
   We can see the credentials using sys.credentials view, for example:
   
   1. select * from sys.credentials  
   
   Figure10: Querying sys.credentials view

2. Server Roles
Used to grant server-wide security privileges to a user. Figure 11 shows the various server roles available for various tasks. There are 9.


Figure 11: Illustration of Server roles in login properties dialogue


Figure 12: Types of server roles & their description


Figure 13: Difference between Server Roles in SQL Server 2012 & SQL Server 2008/2008 R2:

• In SQL Server 2012 there is a new feature introduced in which we can create a server role that was not possible in SQL Server 2008/2008 R2. This is a difference also, that is clearly visible and to make this difference clear Microsoft put a Red-Pin with a fixed server role. These server roles can't be modified when the user-defined server role “MyServerRole” can be modified.
  
  
• When we create a user-defined server role we can add only server-level permissions to that user-defined server role. We can list server-level permissions using the following statement:
  
• 1. SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;  

3. User Mapping

There are the following two further options available:

1. Users mapped to this login
2. Database role membership for "DatabaseXYZ"

a. Users mapped to this login: In this we can specify that the specific login can access which database.


Figure 14: Illustration of user mapped to this login

In this example it is clear that login "ianrox" will be able to access the Adventureworks2008R2 database. Now here the question is whether "ianrox" can perform the tasks on the AdventureWorks2008R2 database. For this database roles are relevant.

b. Database role membership for "DatabaseXYZ": in this section we can specify the activities a login can perform. In the following screenshot we provided the database role "db_backupoperator". It means that "ianrox" can perform backup activity for the Advetureworks2008R2 database.

There are 10 database roles in SQL Server.


Figure 15: Showing database roles

Database roles & their description: The table in Figure 16 describes the database roles.


Figure 16: Showing database roles & their description

Note: a public database role cannot be dropped.

4. Securable
Securables are the resources that we can assign permissions, either at the server level that includes resources like Endpoints, Logins, Server Roles and Databases or at database-level that includes resourcess like Users, Database Roles, Certificates and Schemas.

SQL Server has securable at Schema level also that are called schema scope securable. Tables, Views, Procedures and so on are example of a schema scope securable.


Figure 17: Showing Securables from login properties page

5. Status
In this page we can set the permission to connect to a database engine for a login or we can enable/disable a login. But what is the difference between grant/deny and enable/disable login?

To check the differences I created the following four cases:

1. Select DENY and ENABLED login
2. Select GRANT and DISABLED login
3. Select DENY and DISABLED login
4. Select GRANT and ENABLED login ( It is by default selected when we create new login )

Figure 18: Working on the Status page of Login Properties

i. Select DENY and ENABLED login: When we select this combination we will get the error message “Login failed for user 'yashrox'. (Microsoft SQL Server, Error: 18456)".

ii. Select GRANT and DISABLED login: When we select this combination we will get the error message “Login failed for user 'yashrox'. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)".

iii. Select DENY and DISABLED login: When we select this combination we will get the same error message as in the second combination, “Login failed for user 'yashrox'. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)".

iv. Select GRANT and ENABLED login: It's a default method when a login is created, from this combination we will successfully connect to a database when the right login name & password are provided.

From all these cases I concluded the following things:

• DENY CONNECT SQL will not block members of the sysadmin fixed server role from logging in because deny do not apply to sysadmins. You can check it provide sysadmin role to that login and try with first case in other words deny and enabled. You will be able to login in this case.
  
  
• Connecting to SQL Server is a two-step process as in the following:
  
  First, the login must prove its identity with the correct login name & password. Second, after verifying the identity for the effective permission (grant/deny) will check. If login has connect SQL permission, in other words Grant, then they will be able to connect to SQL Server.
  
  You can check it with the third case where we take deny & disable but we get the error “Login failed for user ‘yashrox’. Reason: The account is disabled". (Microsoft SQL Server, Error: 18470). It clearly shows that the first login is authenticated then connect SQL permission.

Conclusion
This is all about the SQL Server Logins that I tried to explain. I also tried to touch on each aspect of logins. After knowing these things about SQL Server Logins we can dig in more and get more information because when you go into depth you will find some more new things. I tried a lot to make this more interesting, I hope you liked it.

To know more about SQL Server Database Administration click on below links:

• Basics of Database Administration in SQL Server: Part 4
• Basics of Database Administration in SQL Server: Part 5
• Basics of Database Administration in SQL Server: Part 6

Keep smiling & shining & spread the knowledge.

Basics of Database Administration in SQL Server: Part 2

In case you have not had a look at our first article, go through the following link:

• Basics of Database Administration in SQL Server : Part 1

SQL Server Authentication Modes
What is Authentication

Authentication is a process in which we need credentials, in other words username and word, to access the SQL Server.


Figure 1: The very first screen that appears when we open SQL Server Management Studio (SSMS).

When you open SQL Server Management Studio (SSMS) for very first time you will get the following three things:

1. Server Type
2. Server Name
3. Authentication

1. Server Type
There are the following four types of servers:

1. Database Engine: Used for storing, processing and securing data.
   
2. Analysis Services: Used for Online Analytical Processing and data mining functionality.
   
3. Reporting Services: Used for creating interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources.
   
4. Integration Services: Used to do a broad range of data migration tasks. It is a platform for data integration and workflow applications.
   
   
   Figure 2: Illustrating server type in SQL Server

2. Server Name
It can be any name of server by which a server can be identified.

3. Authentication
As we already discussed, it is a process in which we need credentials, in other words username and word, to access the SQL Server that is clearly visible in Figure 3.


Figure 3: Illustrating Windows Authentication and Login

Types of Authentication in SQL Server

There are basically the following two types of authentication in SQL Server:

1. Windows authentication
2. Mixed mode Authentication/SQL Server Authentication

a. Windows Authentication
Requires a valid Windows username and word to access the SQL Server.

b. Mixed mode Authentication
A user can login either via SQL Server authentication or Windows authentication mode to connect to SQL Server.

Example of Windows Authentication Mode:


Figure 4: Illustrating Windows Authentication in SQL Server

The following describes how to check taht we are logged in with Windows Authentication Mode or Mixed Mode.

For this simply execute the following query in SSMS:

1. Use Master  
2. GO  
3. SELECT   
4. CASE SERVERPROPERTY('IsIntegratedSecurityOnly')  
5. WHEN 0 THEN 'Mixed Mode - Allows Both SQL Server or Windows Authentication Mode'   
6. WHEN 1 THEN 'Allows Only Windows Authentication Mode'   
7. END AS [Current Authentication Mode]  
8. GO  

Figure 5: Current Authentication Mode Output


Figure 6: Current Authentication Mode Output

Remember
Remember that when only Windows Authentication is enabled, you can't login with SQL Server Authentication or Mixed Mode.

Let's make it clear with an example.

I have created a new login “yashrox” with SQL Authentication.


Figure 7: Creating a new login from security tab (Step 1)


Figure 8: Choosing SQL Server Authentication/Mixed Mode Authentication for new login “yashrox” (Step 2)


Figure 9: New login “yashrox” created with Mixed mode authetication (Step 3)

Now to check Mixed Mode / SQL Server Authentication we will log into SQL Server with the login "yashrox" that was created with SQL Server / Mixed Mode Authentication.


Figure 10: Trying to login with SQL Server Authentication (Mixed Mode)


Figure 11: Getting error when trying to login with SQL Server Authentication

An error occurred when we tried to login with SQL Server Authentication / Mixed Mode Authentication. Why this happened is because Mixed Mode / SQL Server Authentication is not enabled by default as we saw in Figure 5 and Figure 6 also.

Resolution
The resolution for this problem is to enable the Mixed Mode / SQL Server Authentication, so let's move ahead to enable the Mixed Mode/SQL Server Authentication.

Enabling Mixed Mode/SQL Server Authentication

There are two ways to enable Mixed Mode/SQL Server Authentication mode.

First Way
Step 1
Log into SQL Server with Windows authentication mode with the login name “XYZ\yashwant.kumar" (refer to Figure 4).

Step 2
Right-click on the Server and then click on properties as in the following:


Figure 12: Configuring SQL Server Properties for Mixed Mode Authentication

Step 3
Click on security in the left pane and select SQL Server and Windows Authentication Mode and click OK to save.


Figure 13: Enabling Mixed Mode Authentication

Step 4
Restart SQL Server and try to login with SQL Server Authentication.


Figure 14: Restarting SQL Server after Enabling Mixed Mode Authentication


Figure 15: Checking Authentication mode with query


Figure 16: Checking Authentication mode with other query

Wow! Now this time we are able to login with SQL Server Authentication / Mixed Mode and with both queries we are getting the login mode as Mixed Mode / SQL Server Authentication.

Second Way
Enabling Mixed from Regedit/Registry.

Step 1
Press the Windows key + R to open the Run box. Type regedit and press Enter.

Step 2
Navigate to the registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer.

In the right panel, change the LoginMode from 1 to 2.

1. Windows authentication Only.
2. Mixed mode.

Step 3
Restart your SQL Server instance and you can then connect to the server using SQL Server Authentication.


Figure 17: Enabling Mixed Mode Authentication with Registry

This is something about SQL Server Authentication Modes. Click here to continue with more information regarding SQL Server Authentication, to know more about SQL Server Database Administration follow below links:

• Basics of Database Administration in SQL Server: Part 3
• Basics of Database Administration in SQL Server: Part 4
• Basics of Database Administration in SQL Server: Part 5
• Basics of Database Administration in SQL Server: Part 6

Have a great day ahead, and keep sharing your knowledge. :)

Basics of Database Administration in SQL Server : Part 1

Databases in SQL Server

There are basically the following two types of databases in SQL Server:

1. System Databases.
2. User Databases.

System Databases
System databases are databases that are created when SQL Server is installed. These databases are used for various operational and management activities for SQL Server.

Types of System Databases
There are basically four system databases in SQL Server, master, msdb, tempdb and model that we can see. Apart from that, there is one more system database resource database that is hidden and read-only. Let's move ahead with each system database.


Figure 1: System Database


Figure 2: System Database2

Master Database

• All the system-level information for a SQL Server record by the master database.
• The dbid (database id ) of master is 1.
• The master database has SIMPLE RECOVERY MODEL.
• It is very important database and we must have the backup.
• Without the master database the server can't be started.
• Interview Question: Assume the master database files are missing or inaccessible, will SQL Server start or up?

Answer: No, SQL Server will not start because the master database is the important database and all the configuration and information needed to start the SQL Server is stored in the master database itself hence without the master database SQL server will not start.

• Master database contains information about the server configuration. We can see the server configuration with the following query:
  select * from sys.sysconfigures;
  
• Master database contains the information about all other databases and their location on SQL Server. We can see the information with executing the following query:
  select * from sys.sysdatabases; or sp_helpdb
  
• Master database contains information about logins in SQL Server. The following is the query by which we can see it:
  select * from sys.syslogins;
  
• Master database also contains information about users on SQL Server. The following is the query to see user details:
  select * from sys.sysusers;
  
• Master and mastlog are the logical file names of master database.
  master.mdf ( data file ) and mastlog.ldf are the physical files of master database.

Query to see the physical file location of the master database:

1. SELECT name, physical_name FROM sys.database_files;

Figure 3: Master Database

For more information on the master database use the following link:
https://msdn.microsoft.com/en-us/ms187837

Model Database

• The Model database acts as a template database used in creation of new databases.
• The dbid of the model database is 3.
• By default the model database has FULL RECOVERY MODEL.

Figure 4: Recovery Model

• We can take a backup of the model database.
• Modeldev and modellog are the logical file names of the model database.
• Model.mdf ( data file ) and modellog.ldf are the physical files of the model database.
• The same query can be use to see the physical file location of the model database:

1. SELECT name, physical_name FROM sys.database_files;  

Figure 5: Data Table

Interview Question: A user has created a new database. What will be the recovery model of that database?
Answer: Because the model database acts as a template database, when a user creates a new database it will inherit the property of the model database and as we know by default the recovery model of the model database is FULL (until or unless the user changes it), hence the new database is created by the user with FULL RECOVERY MODEL.

For more information on the model database use the following link:
https://msdn.microsoft.com/en-us/ms186388

MSDB Database

• A MSDB database stores information related to backups, SQL Server Agent information, SQL Server Jobs, alerts and so on.
• The Dbid of the msdb database is 4.
• The recovery model of a msdb database is SIMPLE.
• We can take backup of a msdb database.
• MSDBData and MSDBLog are the logical file names of a msdb database.
• MSDBData.mdf (data file) and MSDBLog.ldf are the physical files of a msdb database.
• The same query can be used to see the physical file location of a msdb database:

1. SELECT name, physical_name FROM sys.database_files; 

Figure 6: MDB database Table

For more information on the msdb database use the following link:
https://msdn.microsoft.com/en-us/ms187112

TempDB

• It stores temporary objects, like temporary tables, temporary Stored Procedures and temporary tables to store sorting and so on.
• The dbid of a temp database is 2.
• The recovery model of a temp database is SIMPLE.
• We can't take a backup of a tempdb.
• tempdev and templog are the logical file names of tempdb.
• tempdb.mdf (data file) and templog.ldf are the physical files of a tempdb.
• Same query can be use to see the physical file location of tempdb:

1. SELECT name, physical_name FROM sys.database_files;

Figure 7: TempDB Table

Interview Question: Why we can't take a backup of a temp database?
Answer: Temp databases, as the name says, are used to do temporary operations, such as tables, Stored Procedures and cursors. Once the operation is over it will be cleared and is minimally logged. A TempDB is recreated everytime SQL is started, so it is always has a clean copy of the database hence backup and restore operations are not allowed for a TempDB.

Interview Question: How you will check to determine if the SQL Server is restarted?
Answer: Check the creation date of the tempdb, if it is new it means SQL Server is started.


Figure 8: Database Properties

We can execute the following query also to check the tempdb creation date:

1. select name, crdate from sys.sysdatabases;

Figure 9: Output

For more information on tempdb use the following link:
https://msdn.microsoft.com/en-us/ms190768

Resource Database

• It is a read-only database hidden from the user. It contains all the system objects that are included with the SQL Server.
• The Dbid of the resource database is 32767.
• The Resource database helps when we do a SQL Server upgrade.
• We can't see the resource database in SQL Server Management Studio but we can see its database file at the OS level by the name mssqlsystemresource.mdf and mssqlsystemresource.ldf in the Binn folder of Program Files.

Figure 10: Microsoft SQL System

We can see the location of the resource database file using the following query also:

1. Use master GO  
2. SELECT  
3. 'ResourceDB' AS 'Database Name',  
4. NAME AS [Database File],  
5. FILENAME AS [Database File Location]  
6. FROM  
7. sys.sysaltfiles  
8. WHERE  
9. DBID = 32767 GO

For more information on the resource database use the following link:
http://blogs.msdn.com/b/vsanil/archive/2012/11/02/resource-database-common-questions.aspx

What User Databases are: User databases are databases created by the user themself.


Figure 11: User Database

This is all about the SQL Server databases. I hope you liked it. To know more about SQL Server Database Administration click on below links :

• Basics of Database Administration in SQL Server: Part 2
• Basics of Database Administration in SQL Server: Part 3
• Basics of Database Administration in SQL Server: Part 4
• Basics of Database Administration in SQL Server: Part 5
• Basics of Database Administration in SQL Server: Part 6

Have a great day. Keep sharing your knowledge !! 

Sql Joins optimum

Query Optimizer is designed in a way to keep a balance between the quality of a query and its execution time. Query Optimizer can’t take hours to evaluate all candidate plans in order to select the best one, but rather it is designed to try within a limited time and select optimum plan.
To select an optimum performing plan, the most important thing for Query Optimizer is “Join Order." At a single time, only two tables can be joined together, and thena  third can be joined with the output from the first two tables, or it is possible to join the two tables separately and then join the result sets. Ordering depends on the nature of join. But which table should be accessed first and which will be joined later -- all these possible patterns are evaluated to get best one.
 
The number of expected plans (candidate plans) is directly proportional to the number of tables joined together in a query. Query Optimizer will always select best possible plan, if expected plans are very few and Query Optimizer is able to evaluate all possible plan within its limited time. On the other hand, if the database is over-normalized and more tables need to be joined, then there are hundreds or thousands of candidate plans and due to limited time Query Optimizer can evaluate a fewer number of plans, and there are possibilities that all evaluated plans were badly performing and Optimizer has selected the best way out of all the bad plans.

How many query plans are possible with a different number of tables in a join?
To join, query tree can be:

• Left-Deep Tree
• Right-Deep Tree
• Bushy Tree

 

The following table shows, how many candidate plans are possible with increasing number of tables in a query (Next time when you write a query, keep these figures in your mind),

Tables	Candidate PlansLeft-deep trees	Candidate Plans Bushy trees
1	1	1
2	2	2
3	6	12
4	24	120
5	120	1,680
6	720	30,240
7	5,040	665,280
8	40,320	17,297,280
9	362,880	518,918,400
10	3,628,800	17,643,225,600

Query with just 8 tables can have more then 17 million candidate plans and you will be lucky to get a good performing plan out of these. That is the reason only 4 or 5 tables are recommended in a single query. Having a higher number of tables means a higher number of candidate plans and as a result more possibilities to have a bad plan for query execution.
One more misconception among a small group of developers is that using simple views can resolve this problem. In fact it’s just an illusion.
Suppose that we have a View, which use five tables in its definition. If we join this view within our query then SQL Server will reopen this view with all its five tables and will use these to join with three tables of query and as a result 17,297,280 plans will be there for Query Optimizer to evaluate.

 

Note: Materialized or indexed views acts like a single table as these are stored physically as single object.
Summary
The number of tables in a query does matter. Keep this number as low as possible by dividing work among queries. Avoid over normalization of your data that will push you to have more tables in every other quer, and last but not least, tables within a simple view are added tothe  final query, and simple views are not a solution. Even derived tables are not a solution for this problem.

Queries perform better when they are written according to Query Optimizer.