Basics of Database Administration in SQL Server: Part 3

SQL Server Logins

In this part, we will try to understand each and everything 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 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:
   

   select name,certificate_id,principal_id, pvt_key_encryption_type_desc from
   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:
   

   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:
   

   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:
  

• 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 resources 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 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.