question

Indigo avatar image
Indigo asked

How to Set up SQL Logins Using Windows Authentication and Windows Group(s)

Does anyone know of a step by step example which automates (script,batch) setting up SQL logins from a "Windows Group"?

I'm trying to avoid manually creating logins for each user separately for a large number of logins.

The objective is to create and use a login group that allows user(s) to login to SQL Server, assign rights, replication and so on without having to specify each domain/user and password. This link shows great information on what I'd like to do, but doesn't really show how to do it.

https://www.mssqltips.com/sqlservertip/1831/using-windows-groups-for-sql-server-logins-as-a-best-practice/

I'm pretty new-b at using windows groups with SQL so simple is good!

loginsql-server-2014
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered

You're not quite there...

What you do is create the *group* in AD, and then, on your SQL Server, run:

CREATE LOGIN [DOMAIN\GROUPNAME] FROM WINDOWS;

(use your real domain / Windows AD group name here, obviously...)

Then, in each database, run:

CREATE USER [DOMAIN\GROUPNAME];

And then grant permissions to *that*.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.