SQL Server Logins Elaborated

Yashwant VishwakarmaSQL DBA
Note: All screenshots applied for SQL Server 2012 Enterprise Evaluation version

SQL Server Logins
In this part we will try understand each & everything related to logins so let’s move ahead to explore SQL Server logins. 

What is Login:
A login is simply a set of credentials that is used to gain access to SQL Server & requires proper authentication. Users need a login to connect to SQL Server or we can say logins are associated to user and the association is done by SID ( security Identifier ). 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.

1.png  Figure1. Illustration of SQL Server Login
Properties of login:
1) It is a server level entity.
 2.png       Figure2: Demonstrating that login is a server level entity
2) It is a set of credentials .i.e. username and password requires.
     Refer figure1.
3) Login information stored in sys.syslogins/sys.server_principals table in master database.
 3.pngFigure3: Showing sys.server_principals output [] output in my case is of 27 rows , I have taken only those rows which covered all type of logins e.g. windows, SQL, certificate mapped and server role, so don’t get confused here ]
Note1: Here I want to emphasize what is the difference between these two 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 which 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.

Note2: 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 alarmed when you see them on your server. From figure3 it is very clear.

4) Logins are associated to users by security identifier (SID) in other words we can say if a database user exists but there is no login associated, then in this case user will not able to log into SQL Server.
We can check it by stored procedure sp_helplogins or sp_msloginmappings
 4.png Figure4: Showing ‘XYZ\yashwant.kumar’ associated to user ‘yashwant’ by SID ‘0x01050000000000051500000094F29E736A3CA94F37C329CD7B540000’ through sp_helplogins in query window & in object explorer also.
5.pngFigure5: Showing output of sp_msloginmappings which also shows that ‘XYZ\yashwant.kumar’ login is associated to user ‘yashwant’.
Exploring More about Logins:
6.pngFigure6: Login Properties Page
Login Properties page divided into five sections:
1) General
2) Server Roles
3) User Mapping
4) Securables
5) Status
1) General:
    When we open General tab of login properties page we can see below information:
a) Login Name: information about the name of login including with authentication types information.
b) Password: it is a password for the login name
c) Specify Old Password: If we want to change the password. Steps to change the password:
  1. right click on login test1 >>click on properties
  2. delete the old password and enter new password in password box
  3. again enter new password in confirm password box
  4. enable the check box specify old password and input old password
  5. click ok. Its done know. Login with new password and check its done.
 7.pngFigure7: Demonstration of changing password of login
d) Enforce password policy: enable this checkbox, if you want to enforce password policy.
    See more about password policy from below link:
e) Enforce Password Expiration: enable this checkbox if you want password expiration time for the login.
f) Mapped to Certificate: Certificates are the way of encryption which are digitally signed object.  
   Certificate provides database level security control.
   we can execute sys.certificates  views to see the certificates e.g.
select name,certificate_id,principal_id, pvt_key_encryption_type_desc from sys.certificates;

Open in new window

8.pngFigure8: Querying sys.certificates
g) 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 view sys.asymmetric_keys, e.g.
select name,principal_id,pvt_key_encryption_type_desc,algorithm_desc from sys.asymmetric_keys

Open in new window

 9.pngFigure9: Querying sys.asymmetric_keys
Note: SQL Server knows 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 []https://en.wikipedia.org/wiki/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) resulting in the more secure the encrypted data is & more bits also means that more CPU resources will be used.
h) 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 credential  by using sys.credentials view, e.g.
​select * from sys.credentials

Open in new window

 10.pngFigure10: Querying sys.credentials view
2) Server Roles: used to grant server wide security privileges to user. Below are the different server roles available for different tasks. These are 9 :
 11.pngFigure11: Illustration of Server roles in login properties dialogue

12.pngFigure12: Types of server roles & their description

Difference between Server Roles in SQL Server 2012 & SQL Server 2008/2008 R2
13.pngFigure13: 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 server role but in SQL Server 2008/2008 R2 it was not possible this is the difference also which is clearly visible. Microsoft put a Red-Pin with fixed server role to differentiate it with user-defined role. These server roles can’t be modified while 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 by executing the following statement:
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;

Open in new window

3) User Mapping: there are two further options available:
a) users mapped to this login
b) database role membership for ‘DatabaseXYZ’
a) Users mapped to this login: In this we can specify that the specific login can access which database.
14.pngigure14: Illustration of user mapped to this login

In this example it is clear that login ‘ianrox’ will be able to access Adventureworks2008R2 database. Now here question arises that ‘ianrox’ can perform which tasks on AdventureWorks2008R2 database. For this database roles come into picture:
b) Database role membership for ‘DatabaseXYZ’:  in this section we can specify that what activities a login can perform. In below screenshot we provided database role ‘db_backupoperator’ means ‘ianrox’ can perform backup activity for Advetureworks2008R2 database.
There are 10 database roles in SQL Server.
15.pngFigure15: Showing database roles
Database roles & their description: Below table describe the database role:-
16.pngFigure16: Showing database roles & their description

Note: Public database role cannot be dropped
4) Securable: securables are the resources which we can assign permissions either at server level which includes resources like Endpoints, Logins, Server Roles and Databases or at database-level which includes resourcess like Users, Database Roles, Certificates and Schemas.
SQL Server has securable at Schema level also which are called as schema scope securable. Tables, Views, Procedures etc. are the example of schema scope securable.
17.pngFigure17: Showing Securables from login properties page
 5. Status: In this page we can set permission to connect to database engine for a login or we can enable/disable login but what is the difference between grant/deny and enable/disable login?
To check the differences I created FOUR below cases:
i) select DENY and ENABLED login
ii) select GRANT and DISABLED login
iii) select DENY and DISABLED login
iv) select GRANT and ENABLED login [] It is by default selected when we create new login ]
18.pngFigure18: Working on 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 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 by default method when any login created, from this combination we will successfully connect to database provided right login name & password.
From all these cases I concluded below 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 i.e. DENY and ENABLED. You will be able to login in this case. 
  • Connecting to SQL server is a two step process:First, the login must prove its identity with correct login name & passwordSecond, after verifying identity effective permission ( GRANT/DENY ) will check. If login have CONNECT SQL permission i.e. Grant then they will be able to connect to SQL Server. You can check it with third case where we take DENY & DISABLE but we got error “ Login failed for user ‘yashrox’. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470). It clearly shows that first login is authenticated then CONNECT SQL permission.
Final Words: This is all about the SQL Server Logins which I tried to elaborate & touch each aspect of logins. After knowing these things about SQL Server Logins we can dig more & get more information because when you will go to depth you will find some more new things. I tried a lot to make this more interesting, hope you will like it.
Keep smiling & shining & spread the knowledge.
Have a Great Day Ahead :-)
Yashwant VishwakarmaSQL DBA

Comments (2)

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Very well laid out, and excellent use of images to support the text.  Voting Yes.


Hi Jim & EE Team,
Greetings of the Day :-)
Thank you & EE Team for appreciating my effort. It is just because of your suggestions which I followed and it looks cool now & finally accepted by EE Team.
Thanks once again feeling woooo... :-)

Wishing you & EE team to always rise and break all the waals of milestones.
Keep rising & shining & stay blessed :-)

Yashwant Vishwakarma | www.sqlocean.com

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.