#26.Difference between Logins and Users in SQL Server

Difference between Logins and Users in SQL Server

Why?

Although the terms login and user are often used interchangeably, they are very different. This is one of the interesting thing designed in SQL & has been enhanced so much. The relation of Logins & users is dealt on this post. Whenever an end user requesting access to any database or to a server level role a DBA should understand each & every aspect of the roles & its responsibilities before granting the requested access.

How?

            We are going to get help of 2 system tables namely syslogins & sysusers. The system table SYSLOGINS will give details about all logins present in SQL whereas the system table SYSUSERS will give you details about user level accesses & privileges associated with that particular database against which we run a select on sysusers. Let’s begin.

LOGINS & USERS:

 A login is used for user authentication

A database user account is used for database access and permissions validation.

Logins are associated to users by the security identifier (SID).

A login helps an end user to get into SQL server with his Windows or SQL authenticated ID. A User helps an end user to connect to any database in a SQL server after his/her login connected to the SQL server already. All logins in a SQL server will be stored in Master Database & the Authentication of the same is done at SQL server level.

             A login is required for access to the SQL Server server. The process of verifying that a particular login is valid is called "authentication". This login must be associated to a SQL Server database user. You use the user account to control activities performed in the database. If no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to SQL Server. The single exception to this situation is when the database contains the "guest" user account. A login that does not have an associated user account is mapped to the guest user. 

Scenario:       

            We are going to create a LOGIN called SQLCONNECT. Then do a select from sys.syslogins with selective columns. You may see no Server level roles are assigned to this Login. For example SQLCONNECT LOGIN is neither sysadmin nor security admin.

            CREATE LOGIN SQLCONNECT WITH PASSWORD = 'pass@123';

select sid,name,dbname,hasaccess,loginname,sysadmin,securityadmin from sys.syslogins

Now login to SQL using SQLCONNECT Login & let’s try to connect to Servercounts database & run a select against test table.

           

This is because there is no user created for Login SQLCONNET & mapped with it. Now we create a user corresponding to Login SQLCONNECT & will grant database owner rights on servercounts database.

CREATE USER SQLCONNECT FOR LOGIN SQLCONNECT

exec sp_addrolemember 'db_owner', 'SQLCONNECT'

If we run same select now, we will be able to see records popping out.

Thus we had seen the clear difference of SQL LOGINS & SQL USERS now.

SCENARIO 2:

Is that means all SQL LOGINS should need a SQL USERS created separately? The answer to this question is a big ‘NO’. Any Login which has SYSADMIN rights don’t need an explicit user to get created. By default all SYSADMIN privileged Logins will be mapped to dbo user present in SQL server. Let’s see how this works.

         When you do a select on SYS.SYSUSERS on the database servercounts you may see that as of now SQLCONNECT login is mapped to this database using a SQLUSER.

use servercounts

select Name,sid,hasdbaccess,issqluser,issqlrole from sys.sysusers

Now I am going to drop the sql user ‘SQLCONNECT’ from Servercounts database & will grant SYSADMIN right to the Login ‘SQLCONNECT’

use servercounts

Drop user SQLCONNECT

EXEC master..sp_addsrvrolemember @loginame = N'SQLCONNECT', @rolename = N'sysadmin'

If you try to do a select on test table in Servercounts database, you will get the result now but we had already dropped the SQLCONNECT user. This had become possible as any SYSADSMIN on a SQL server will automatically resolve to dbo user present on all databases. Let’s check it.

Again by using sys.sysusers ,You may see that no such user called SQLCONNECT exists but still the output will be derived for the select ran on test1 table in Servercounts.

use servercounts

select Name,sid,hasdbaccess,issqluser,issqlrole from sys.sysusers  where name in( 'dbo','SQLCONNECT')

Thus the difference of SQL Logins & Users are analysed here.

========================================================

Thanks for coming till this point!!
VSG
SQL DBA