I suggest you do some reading to understand a few basic components for SQL Server security.
- SQL Server Logins
Logins are individual user accounts for logging on to the SQL Server Database Engine. SQL Server and SQL Database support logins based on Windows authentication and logins based on SQL Server authentication.
- Authentication mode
There are two possible modes: Windows Authentication mode and mixed-mode. Windows Authentication mode enables Windows Authentication and disables SQL Server Authentication. Mixed mode enables both Windows Authentication and SQL Server Authentication. Windows Authentication is always available and cannot be disabled.
- Database users
Logins are granted access to a database by creating a database user in a database and mapping that database user to login. Typically the database user name is the same as the login name, though it does not have to be the same. Each database user maps to a single login. A login can be mapped to only one user in a database but can be mapped as a database user in several different databases.
Database users can also be created that do not have a corresponding login. These are called contained database users. Microsoft encourages contained database users because it makes it easier to move your database to a different server. As a login, a contained database user can use either Windows authentication or SQL Server authentication. For more information, see Contained Database Users - Making Your Database Portable.
- Security identifier (SID)
Each login to SQL Server has a unique identifier, think like a primary
key, to identify that login from every other login. This is true
whether the login is a SQL Server-based login, a Windows user, or a
Windows group. That unique identifier is called the SID, which is
short for security identifier. In the case of a SQL Server-based
login, the SID is generated by SQL Server. For Windows users and
groups, the SID matches the SID in Active Directory.
In your case, in the four databases, there is a user corresponding to the AD login that you are creating. As Andrew explained above, you can check that user's existence. I found this Q&A has some scripts that can help you list all mapped users for given login.
by default but when I create SQL login, it does not get.
Two reasons. No user corresponding to the login exists. Or, the Security identifier between the SQL login and the user does not match.
Remaining 2 DB are the backup of some other physical server’s databases restored in this server.
There is no user corresponding to the AD account, most likely because the other server never had the same AD login.
I want that no one gets access to any DB unless I give it. Plz let me know how do I achieve this
Remove the users from the databases that you do not want to have access to.