116

I have a .NET application which is connecting to the SQL Server using Windows authentication.

We cannot use SQL Server authentication in the application. We have lot of Active Directory users there for our project. So we have to create separate login account for each Active Directory users in SQL Server rather than creating separate login account for each AD users, is there any way to use the active directory user group in SQL Server?

TylerH
  • 20,799
  • 66
  • 75
  • 101
Jibu P C_Adoor
  • 3,304
  • 9
  • 30
  • 36

3 Answers3

173

In SQL Server Management Studio, go to Object Explorer > (your server) > Security > Logins and right-click New Login:

enter image description here

Then in the dialog box that pops up, pick the types of objects you want to see (Groups is disabled by default - check it!) and pick the location where you want to look for your objects (e.g. use Entire Directory) and then find your AD group.

enter image description here

You now have a regular SQL Server Login - just like when you create one for a single AD user. Give that new login the permissions on the databases it needs, and off you go!

Any member of that AD group can now login to SQL Server and use your database.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Is there any chance to create groups in mysql like above? – uzay95 Jul 17 '12 at 11:14
  • @uzay95: sorry, no idea - I don't know MySQL. But since it's a cross-platform database, I doubt it has something very Windows-specific like that... – marc_s Jul 17 '12 at 12:12
  • 8
    This doesn't work for me. I have a Windows Group "DataAccess", which "UserX" is a member of. I created a login for DataAccess group and set the login's user mapping to have public, datareader, and datawriter access on a database. The user can't log in. If I add the user specifically, and assign the exact same mapping permission, then he can log in. When I click on Securables > Effective Permissions for the group, it says "Cannot execute as the server principal because the principal 'GroupName' does not exit, this type of principal cannot be impersonated, or you do not have permission." – Triynko Jun 05 '13 at 19:48
  • Is there a way, how to do it in TSQL? I need to add this user everytime I restore backup from another computer. – Tomas Kubes Nov 24 '13 at 13:11
  • @Triynko it looks like it only works for AD groups, not local groups – Snowburnt Jun 19 '14 at 15:19
  • In MSSQL 2017 the Groups option doesn't appear in Object Types. How can this be done? – imqqmi Dec 11 '17 at 11:24
  • I have attempted to use this solution and it does not work for me. When I click the "Ok" button while the group is selected, which is an internal directory, nothing happens. Do I need to set up something else in the sql server management studio before this step? – Cheddar Mar 18 '19 at 12:35
  • I have checked groups, changed to entire directory but I can't add my group: An object (user, group, or built-in security principal) with the following name cannot be found: "IT". It works if I add an AD user though. – Paul-Sebastian Manole Oct 14 '19 at 06:10
  • "Any member of that AD group can now login to SQL Server and use your database." What is returned by `SELECT SYSTEM_USER` when the login is an AD Group? I cannot test this at the moment. – Tim Jul 16 '20 at 15:03
  • 2
    @Tim: the user's name - it's still the user that logs in - he's just not permissioned directly - as a user - but by being member of that group that has permissions – marc_s Jul 16 '20 at 15:59
22

You can use T-SQL:

use master
GO
CREATE LOGIN [NT AUTHORITY\LOCALSERVICE] FROM WINDOWS WITH
DEFAULT_DATABASE=yourDbName
GO
CREATE LOGIN [NT AUTHORITY\NETWORKSERVICE] FROM WINDOWS WITH
DEFAULT_DATABASE=yourDbName

I use this as a part of restore from production server to testing machine:

USE master
GO
ALTER DATABASE yourDbName SET OFFLINE WITH ROLLBACK IMMEDIATE
RESTORE DATABASE yourDbName FROM DISK = 'd:\DropBox\backup\myDB.bak'
ALTER DATABASE yourDbName SET ONLINE
GO
CREATE LOGIN [NT AUTHORITY\LOCALSERVICE] FROM WINDOWS WITH
DEFAULT_DATABASE=yourDbName
GO
CREATE LOGIN [NT AUTHORITY\NETWORKSERVICE] FROM WINDOWS WITH
DEFAULT_DATABASE=yourDbName
GO

You will need to use localized name of services in case of German or French Windows, see How to create a SQL Server login for a service account on a non-English Windows?

Community
  • 1
  • 1
Tomas Kubes
  • 23,880
  • 18
  • 111
  • 148
  • In case you also need to set the database level login/principal, here's another answer I found useful http://dba.stackexchange.com/a/2578/60876 – Agostino Oct 05 '16 at 10:31
  • I wonder if this can be used to grant a certain active directory group read access to a certain schema - in pseudo sql `CREATE LOGIN ... WITH DEFAULT_SCHEMA` the dots `...` have to reference a certain ad group. We have several groups and would like to give an active directory group `accountants` access to schema ´A´ but not to schema ´B´ and ´C´. Likewise group ´billing` should only have access to schema ´B´. As a frontend we are using msft access. – surfmuggle Oct 27 '17 at 08:54
  • In my case I had to put a space between LOCAL and SERVICE to make command pass without errors. – Daniil Iaitskov Oct 25 '22 at 23:26
-3

Go to the SQL Server Management Studio, navigate to Security, go to Logins and right click it. A Menu will come up with a button saying "New Login". There you will be able to add users and/or groups from Active Directory to your SQL Server "permissions".

TylerH
  • 20,799
  • 66
  • 75
  • 101