1

I am coming from a security standpoint. I see quite a lot of SQL DBs with the entire domain "Users" group ("XXX Domain\Users") added as a login account in SQL.

Does this mean that all users in the XXX domain can now access the SQL server? Is this a security risk? Will removing it impact my applications?

I have DBAs telling me that this is not a security risk.

newbie
  • 113
  • 5
  • Take a look at http://dba.stackexchange.com/q/2572/57247. This might be where the dba is coming from. There are multiple steps in allowing users to have access through a group. – James Rhoat Jan 03 '17 at 03:30
  • my understanding is that the applications will "talk" to the DB via a service account or something and that end users should not even be granted access to the database. Any modifications that are needed should go through the application. Access rights should be controlled at the app level – newbie Jan 03 '17 at 03:33
  • You are correct but depending on some scenarios specific users might need access to the backend. Additionally, if you are sure it's not needed I don't see the harm in removing it. But as the other question mentioned if they aren't granted permissions it shouldn't be a security flaw. But he might just be taking a cautionary approach. Did you ask him why it should or should no be there? – James Rhoat Jan 03 '17 at 04:40
  • Some of the replies I get are that these end users do not have access to the SQL management studio software and hence, even if they have the access rights, they cannot access the DB. However, it should still be removed if this is the case. I am also unsure if removing it will impact any applications... – newbie Jan 03 '17 at 05:28

2 Answers2

2

Don't remove any SQL Login's until you are sure they are no longer required.

Now you might be mixing two definitions.

  • SQL Server databases contain database users
  • SQL Server instances contain SQL Logins

SQL Server Logins

  • A SQL Server login created on a SQL Server instance can either be a Windows Authenticated User or Group, or it could be a simple SQL Server account/login.

  • A SQL Server login will have its own password and rules.

  • A Windows Authenticated user or group created as a SQL Server login will be linked to the corresponding Windows Account and does not contain a password. It validates either against the Domain or against the Server the user/group belongs to.

  • A SQL Server login has permissions at SQL Server instance level.

Database Users

  • A database user is limited to permissions inside the database. (There are exceptions).
  • You can assign a database user DML or DDL permissions and grant EXECUTE permission on stored procedures.
  • You can assign a database user to database roles
  • You can assign a database user to a schema (default: dbo)

Linking database users and SQL Server logins

There can be a link between database users and SQL logins, but there doesn't have to be.

Let's give you a few examples

  • You could have the SQL Server login DOMAIN\Users with VIEW SERVER STATE permissions for the instance, but otherwise not linked to a database via database user. All domain users can then query the state of the SQL Server.

  • You could have the SQL Server login DOMAIN\Users linked to a database user TelephoneBook_Reader in the database TelephoneBook. The database user TelephoneBook_Reader might have SELECT permissions on the table Employees in the database. If you remove the DOMAIN\Users from the SQL Server instance, then nobody will be able to query the telephone book.

  • You could have the SQL Server login DOMAIN\Users linked to a database user DOMAIN\Users in the database PsuedoDB. The Database user DOMAIN\Users might have permissions to select, insert, update and delete for the schema Inventory.

  • ....

Answering your Question

No, just because a Domain group is assigned to a database via a SQL Server login linked to a Database user, does not have to be a security risk. It might be the easiest way to grant a lot of users access to a database.

Yes, if a Domain group has been granted permissions to access a database (via SQL Login and Database user) and to query information it should not be allowed to, then this can be a security risk.

As with so many things in SQL Server: It depends.

Check the database permissions assigned to the SQL Server login DOMAIN\Users and verify that these permissions are required.

From a hacker's standpoint: Every permission granted can be pose a security risk.

John K. N.
  • 17,649
  • 12
  • 51
  • 110
1

https://stackoverflow.com/questions/1134319/difference-between-a-user-and-a-login-in-sql-server

I'm pretty certain this will explain the situation for you.

A login allows you access to the server

A user is required to access a database though

As the accounts you mention are login accounts they have no access to any database instances you might have. Which is why your dba's don't consider it a risk.

If you don't quite understand what I mean by this take a look at this MSDN Creating DB users

You see if you're using AD DS which based on your tagging, you are. You will have user groups, security groups etc and DB users assigned to groups have roles each with specific Rights and privileges. If privileges are not explicitly given the default is deny.

To clarify if the user has not been given a ROLE or had privileges exclusively given to them, they won't have any rights.

Without actually seeing your config and which specific part you're uncertain/worried about it's hard to give you a definite answer in all honesty.

Based on experience if it was set up correctly you won't have an issue.

HOWEVER

IF you remove DOMAIN/USERS from being able to access the server. No users belonging to this group will have access. They would have to be in a different usergroup like DOMAIN/DBA or something.

Community
  • 1
Philip Mather
  • 11
  • 2