0

User created from Active directory can access all instances of databases in SQL Server without any grants. Was shocked to see this behaviour on our environment.

Example. We have a database named MyDB It has 2 instances named Ins1 Ins2

Now if I add and AD user lets say

Domain/username

The domain/username can access both Ins1 and Ins2 even though , I have not mapped it anywhere. Any suggestions will be highly appreciated.

Dan Guzman
  • 28,168
  • 2
  • 44
  • 68
Yatharth Sah
  • 1
  • 1
  • Please check this --> https://www.sqlservercentral.com/blogs/assign-sql-service-account-with-group-policy – Learning_DBAdmin Jul 12 '21 at 11:35
  • I removed the MySQL tag since your question is about Microsoft SQL Server. – Dan Guzman Jul 12 '21 at 11:37
  • Could you please be a bit more specific in your description? For SQL Server Database Users you would use the notation: SQL Server Instance | Database | Security | Users and from the SQL Server Logins perspective: SQL Server Instance | Security | Logins. Where are you adding what exactly? Unless of course you are asking about Oracle where the notation would be Database Instance | Users. Thanks. – John K. N. Jul 12 '21 at 11:45
  • I answered a similar question a few weeks ago. See this answer. https://dba.stackexchange.com/questions/295214/ad-login-in-sql-can-access-all-the-databases/295219#295219 – SqlWorldWide Jul 12 '21 at 12:36
  • 1
    Use XP_LOGININFO on the account in question to see what group membership they have that may be granting them access. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-logininfo-transact-sql?view=sql-server-ver15 – Jonathan Fite Jul 12 '21 at 12:41

1 Answers1

1

Are you sure that User isn't part of an Active Directory Group that's already mapped to those databases, for example the default Domain Users group? Users inherit the SQL Server permissions of the Active Directory Groups they belong to as well.

Also if you could elaborate on what exactly they have access to in the database, that would be helpful. Anyone who can login to the server instance should be able to see the list of databases, even with no access to actually interact with them.

J.D.
  • 37,483
  • 8
  • 54
  • 121