1

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

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:

7
  • Thanks for sharing this knowledge. I have idea about login and user but this is really more helpful.
    – Mustafa
    Jul 3 at 18:59
  • When I m creating login , an user with the same name gets created in DB1 but I deleted that user from DB1 n now I check login property I don’t see any DB mapped there but still after logging into ssms I can access 4 DBs. I will find out the user under which the DB is getting accessed after logging into and keep you informed. Once again many thanks to both of you the help
    – Mustafa
    Jul 3 at 19:05
  • You are welcome. I added a link in the answer which will help you find the mapped user for the given login. Jul 4 at 0:54
  • Thanks for your valuable time and suggestions. I sorted out the problem. All my 4 DBs have BUILTIN/Users window group which is created at the time of OS installation and it allows all authenticated users of window and AD to access SQL Server instance. I ran below query to find out the user which is accessing DB under my Login is part of which windows group/role
    – Mustafa
    Jul 4 at 18:10
  • select [principal_id] , [name] , [type_desc] , is_member(name) as [is_member] from [sys].[database_principals] where [type] in ('R','G') order by [is_member] desc,[type],[name]
    – Mustafa
    Jul 4 at 18:10
1

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?

2
  • Thanks a lot. I will try as you suggested and keep you informed
    – Mustafa
    Jul 3 at 18:48
  • Hi Andrew. The problem sorted out. I used your query to find out the windows group that was by default giving access AD users.
    – Mustafa
    Jul 4 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.