SQL Server – Should Domain ‘Users’ Group Be Removed from Logins for Database Security?

active-directoryloginsSecuritysql server

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.

Best Answer

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.

Related Solutions

SQL Server – How to Refresh AD Security Group on Permissions

The user need to log off of their workstation and log back on. That's why the changes appear to take effect the next day. The reason for this is that when the user logs on the next day they get a new token from the domain controller and this token contains the list of domain groups that they are a member of. This token with the list of domain groups is only updated when the user logs in to their computer, so if the user never logs out the token isn't ever updated.

There's also multi-site domain replication delays which need to be taken into account if your domain controllers are in different physical locations.

Sql-server – Adding the domain account to a security group on the SQL Server computer that has sufficient privileges to log on as a service

To answer your questions directly:

2) In order to add a login to the sysadmin fixed server role you can utilize ALTER SERVER ROLE ... ADD MEMBER (2012) or sp_addsrvrolemember (pre-2012). Here would be an example:

exec sp_addsrvrolemember 'yourLoginName', 'sysadmin';

3) Without having to reiterate what's found in the following documentation, take a look at this MSDN reference on the service permissions for each SQL Server service.