AD login in SQL can access all the databases

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

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

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

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

Method 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?

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply