AD login in SQL can access all the databases

Question

On my sql server, when I m creating sql Logins for any windows AD user, the user gets access to 4 DBs out of 6 DBs of that instance by default but when I create sql login, it does not get. The windows users do not get automatic access to the remaining 2DBs.

First DB from These 4 DBs was created in this instance itself and remaining were restored from the backup of first one.

Remaining 2 DB are the backup of some other physical server’s databases restored in this server. I want that no one gets access to any DB unless I give it. Plz let me know how do I achieve this

Answer 1

I suggest you do some reading to understand a few basic components for SQL Server security.

1. 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.

2. 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.

3. 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.

4. 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.

Ref:

• Getting Started with Database Engine Permissions
• Identifying the Tie Between Logins and Users by K. Brian Kelley

Answer 2

It sounds like you had a DB user that is already linked to a login for an AD group which covers the Windows users you are adding.

From one of your 4 DBs which your Windows users are getting access to, you can use the following query to list existing logons and the DB users they will be using.

select  sp.name, dp.name
from    sys.server_principals sp
join    sys.database_principals dp
  on    sp.sid = dp.sid

You can also use xp_logininfo with one of your Windows users to see which login they are being resolved to to connect to your DB:

EXEC xp_logininfo 'domain\username';

Once you've identified the login that they're using, you can decide how to take action - should the login be removed completely? Should the user be removed from the 4 DBs? Is the group used by legitimate processes but the privilege shouldn't be given to the whole group?