Using Windows Groups for SQL Server Logins as a Best Practice

By:   |   Comments (19)   |   Related: More > DBA Best Practices


We're taking a break from the tips format this month to provide you with some Best Practice articles for the many aspects of Microsoft SQL Server development, administration, and support. This article will focus on the use of Windows Groups for security in your SQL Server instances in lieu of relying on SQL Server logins for administering your security contexts.


First we should explain those terms I just tossed out to you without warning. What is a Windows Group? SQL Server Login? I can see some of you throwing your hands up already. It's really simple, trust me.

Inside of SQL Server you have two options for allowing clients to access your databases: Windows (also known as Trusted) Authentication and SQL Server Authentication:


The main difference between the two authentication schemes is that Windows authentication relies on your domain's security architecture while SQL Server authentication adds an additional process for authentication by requiring a hard-coded login and associated password for connectivity to any SQL Server instances or databases.

This may make SQL authentication sound like a more-secure model for tightening access to your databases; this is not necessarily the case. These logins and associated passwords must be saved somewhere in the client/application structure whether it is in an .ini file, a DSN (data source name) file, registry setting, or (forbid) hard-coded into the application code. Very likely this information is exposed quite easily to anyone with access to the client computer or application server.

In the case of hard-coded logins and passwords or .ini files this information would most-likely be common to all installations of the particular application. This is quite troubling in the case of turnkey solutions purchased from third-party vendors outside your organization. Each customer has the same security information to connect to your databases; they just are missing one link in order to do so: server connectivity. A middling hacker could easily overcome this information with little effort. This is really no different in my opinion than securing the System Administrator login (sa) that is native to each and every SQL Server installation with a blank password. This is particularly the case when you have a single login associated with an application; not so much the case when each client or user has their own login id. Though that still does still open yourself up for hacking and also causes additional overhead as I will discuss below.

By using Windows authentication you pass on the domain login information from any client attempting to access databases on a SQL Server instance. If the user is authenticated to the domain correctly then SQL Server will "trust" that the Security Administrators in your domain are doing their job and that the user is who they say they are. SQL "trusts" the login process in the domain and that is why you'll often see Windows authentication referred to as "Trusted Authentication".

So what does this afford the SQL Server Database Administrator or Programmer/Developer? For the Programmer/Developer it means that you don't need to code in the user login and password into the application construct. In whatever connection scheme you use you simply need to state the application is going to utilized "trusted" security in order to connect to the database. Examples of the connection strings for both SQL authentication and Windows authentication below highlight this point:

For this example I'll create two logins, the first will be using SQL Server authentication, the second will rely on Windows authentication.

USE [master] 

CREATE LOGIN [Timothy]  
USE [master] 


You'll note that when creating a SQL-based login you must specify not only the login name, but an associated password. The DEFAULT_DATABASE clause directs the connection to a specific database if no database is specified in the connection string within the application construct the DEFAULT_DATABASE statement is used for either SQL or Windows security constructs.

Meanwhile, the CHECK_EXPIRATION and CHECK_POLICY clauses, associated only with SQL-based security, direct SQL to require the password strength and expiration periods from your domain security rules to either be ignored or enforced. Meanwhile, a Windows-based login only requires that you declare the domain/login associated with the login you're creating. The password is irrelevant, because the user will be logging into the domain first; authentication there must be successfully completed before connection to your SQL Server instance.

With these two logins in place on your instance you need to grant rights to the default database (at least) if you want to allow them to be useful. The code for doing so is below, first for the SQL authenticated login followed by the login based upon Windows authentication:



EXEC sp_addrolemember N'db_datareader', N'Timothy'; 


EXEC sp_addrolemember N'db_datareader', N'SAURON\Timothy';

You'll see that I'm creating a user in a very similar fashion between both security schemes. Short of the login names being different there is no differences in how users are created from one security scheme to another. The differences lie at the login level of SQL Server's security model. In both cases I created a user with the same name as the login. This is not a requirement. I could have just as easily created a login named Timothy_Windows from the Windows-based login. The code would look like the following if that was the case:


EXEC sp_addrolemember N'db_datareader', N'Timothy_Windows';;

So now that we've laid the foundations in my test instance for the Timothy login (SQL-based) and SAURON\Timothy login (Windows-based) how does this bode for the Programmer/Developer? I alluded to simplicity in the connection string for Windows v. SQL logins. This is evident in the code from two sample connection strings:

SQL-Based Security: "Driver={SQLServer};Server=Sauron;Database=MSSQL Tips;Uid=Timothy;Pwd=Timothy;" 

Windows-Based Security: "Driver={SQLServer};Server=Sauron;Database=MSSQL Tips;Trusted_Connection=yes;" 

We don't have to expose the login or the password by using Windows-based security. Furthermore you don't need to customize a connection string per distinct user.

For the DBA, using Windows-based security means less overhead. Password management can be a nightmare for a DBA. Constantly fielding questions from users asking for a password reminders when they lose them require you to interrupt your other work to answer their immediate needs. It also requires you to keep a secondary password management system current since the password is not visible for a easy review by even the DBA. With Windows security that is all handled by the Domain Security Administrators - right where that overhead belongs.

Windows Groups make life even better for the DBA. I was about to say my love for Windows Groups rivals that of my love for bacon or chocolate ice cream, but that would be blasphemy. Why do I have such deep affection for Windows Groups? What are Windows Groups? Let's look at that...

Windows Groups are logical constructs at the domain level that organize individual domain logins into distinct entities. Individual logins can then be transitioned in and out of the group by the Domain Security Admin. As a DBA you can create a login based upon a Windows Group, rather than a Windows login. The code is the same, with the exception of one concept that may cause concern: the DEFAULT_SCHEMA clause.

You can not set a default schema for a SQL database user based from a Windows Group. This means that you must rely on solid application coding for fully-qualified naming conventions for all objects or having discrete object names across all schemas. Now for the bonus of using Windows Groups: it allows the DBA to be fully-hands-off when it comes to dealing with the comings and goings of individual users in your organization! It pushes that responsibility back onto the Domain Security Admins.

Think about this for a second: Bob is hired into the Accounting Department in your company and needs to have access to five different databases that are used in the department. Even with Windows logins you would need to create a login and then create a user for that login in each database. Compound that if the databases reside on different SQL Servers. With a login based off of a Windows Group, you create a single login for the Group. Assign the rights accordingly, and then as Bob or other users transition in and out of departments or employment within the organization, the Domain Security Administrators add and remove them from the Windows Group that feeds the SQL login and database user in each database. You as the DBA do not need to be notified when Bob leaves the company (in this example). Furthermore, it reduces the use of logins by replacing tens, hundreds, or thousands of individual Windows-based logins in your SQL environment with a single login based off of the Windows Group.

The basic structure is outlined below:


SQL Server knows only of the Windows Group underlying the login. It cares not for whom is a member of that group. It assumes that it is all handled by the domain authentication process (remember the concept of trust?) As individuals transition in and out of the group, you as the DBA don't know and don't care. Even better, you don't need to do a darn thing.

Next Steps

So let's recap.

  • SQL Server allows you to have two types of logins: those based upon Windows accounts and those that are based solely upon creation of a login and password within SQL Server.
  • Windows-based logins are preferred when possible because it removes a layer of authentication, it streamlines the login process, and prevents the DBA having to be the go-to source for remembering passwords.
  • Windows-based logins can be created from Windows Groups, which takes the process one step further by eliminating the need for the DBA to be called into action when users' database needs change. Windows Group-based logins are preferred, but with the caveat that fully-qualified naming is used when referencing database objects, or so long as database objects are discretely named across all schemas.

This concludes our first Best Practices Tip on SQL Server Logins. Please stay tuned to for more Best Practices tips. I've also provided links below that may be appropriate to the discussion.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, May 9, 2019 - 4:53:30 PM - Par Back To Top (80010)

Thanks - this article was really helpful.

But, two folks (charlie and Teresa Schroat) asked a question I'm hoping someone can answer.  Since using a Windows group will cause multiple users to map to one SQL Server login, is there any way to capture auditing/accountability information?  I have certain tables that we want to know who the last modified is for every record.

I guess if it's not possible with Windows/SQL Server, we can have the app developer pass the userId into the database server (as just another field) and do it manually. Prefer not to have to do that though...

Thursday, October 25, 2018 - 10:20:09 AM - Phil H Back To Top (78056)

 One concern associated with using Windows Auth: a user belongs to an AD group that has permissions to the database. What's to stop that user from connecting to the instance from outside the intended application (using code or a tool like Access or even SSMS), and making changes through that channel?

Thursday, September 27, 2018 - 3:56:09 PM - Greg Robidoux Back To Top (77756)

Hi Mustafa,

It is better to use Windows authentication for SQL Server if possible.  There are more security settings at the Windows level and this is approach is seen as a best practice for SQL Server.


Thursday, September 27, 2018 - 3:42:08 PM - mustafa rasol abbas Back To Top (77755)

 when i finished reading this article i come to conclusion that when i install the sql data base server on machine with windows server 2012 R2 and domain controller, i should configure my sql instalation to be windows authintication, am i write. please let me know. thanks

Monday, July 30, 2018 - 8:15:24 PM - Teresa J Schroat Back To Top (76907)

Curious how you would know which user is associated with the action if domain\group is used where auditing is concerned and question 2 if user is on more than one group with different permission levels.

Friday, November 10, 2017 - 3:24:44 AM - MichelP Back To Top (69508)

 I wonder when a Windows login (WL-A) is declared in 2 (or more) Windows groups (Wg-1, Wg-2) and there is one Sql login for each group (Sl-Wg-1, Sl-Wg2) with differents permissions, what's the resultant permission ?


Thursday, November 9, 2017 - 6:37:33 AM - Anders Ebro Back To Top (69478)

Thank you for writing this up, and explaining it in a very clear way. (not the first site I've visited for this info). One thing I did find missing was the SQL syntax for creating the group based login, vs the windows based login.


Wednesday, February 15, 2017 - 6:07:57 PM - Karen Clark Back To Top (46375)

Thank you so much for this information.  It was exactly what I needed for an overview and is very well written.  I look forward to learning more from you.   


Friday, October 9, 2009 - 11:14:23 AM - charlie Back To Top (4167)

Thanks for the article on using Windows groups, Tim!

As a software developer (not a DBA expert) working on an applications that use SQL Server as the database server, one concept I've often heard told to the clients when setting up users  for SQL Server is that they should set up a separate user for each end user, so that they will be able to tell who performed given actions when looking at any audit logs or anything. The idea was that if everyone logged in using a single shared user, there would be no way to determine which person actually performed a given action. 

Is that not an issue when using the Windows groups solution, since SQL Server only sees the one login/user associated with the group?  It certainly seems to lead to cleaner management of privilages and access. Maybe the need to determine which individual was performing a specific action was being overstated?

 Thank you again for the great article.


Friday, September 4, 2009 - 5:13:09 AM - timmer26 Back To Top (4002)

Nice to hear that Biz.

Thursday, September 3, 2009 - 6:38:56 PM - bisjom Back To Top (3998)


I was trying to implement the security with our new environment and i started using windows groups. It is good.. Glad to know that i chose the best practise one...


Thursday, September 3, 2009 - 1:03:30 PM - peteo Back To Top (3997)

Very cool. Thank you very much for your dialogue on this. It did answer several question i had!!!

Thursday, September 3, 2009 - 12:55:11 PM - timmer26 Back To Top (3996)

Precisely; on all counts.

Thursday, September 3, 2009 - 11:42:14 AM - peteo Back To Top (3994)

OK I think I get what you are saying here. When you create a table you should specify the proper schema to use instead of letting it use the default user schema. Is this correct?


The problem is, and this is specific to create ESRI SDE feature class tables, is that it is done through the application. I do not think thier is a way to specify the schema, it just uses the default one associated with that particular logged in user.

So in this instance it is poor application control, correct? 


Thursday, September 3, 2009 - 9:20:35 AM - timmer26 Back To Top (3993)

The issue with dbo schema being required in you application highlights the concern about best practices (ha! our root topic!) around developing with fully-qualified naming.  In this case it sounds like your application is making calls to objects without a defined schema: SELECT * FROM tableA instead of SELECT * FROM schemaX.tableA for example.  Therefore SQL Server has to make the assumption the the dbo schema is to be used if both dbo.tableA and schemaX.tableA both exist in the database.

If you're developing an application for use and separating objects out in schemas, there application's schemas should be organized by a role or business purpose, not by a developer name such as accounting.users or payroll.history.  Not dave.users and paul.users or paul.history.

Thursday, September 3, 2009 - 8:40:19 AM - peteo Back To Top (3992)

This is probably were my being a neewb is not helping the matter but this still seems to be a problem to me.

In the scenario I described the application wants all of the tables to be in the .dbo schema, else it has some real issues.

Also if you have 4-5 people who are adding tables it seems to me it will be a real management nightmare with all the different schema's you now have in your database.

 Am I missing something here?

 And again I would love to make this work. I hate having to manage individual users. Using groups is SO much better from a management standpoint.

Thursday, September 3, 2009 - 8:16:38 AM - timmer26 Back To Top (3991)

Peteo, that is why I mentioned that you really need to make sure that usage of fully-qualified naming is enforced when using groups.  The users need to explicitly use the schema name as a prefix when querying or else unintended results will occur if, for instance you have dbname.schema1.tableX and dbname.schema2.tableX.

Thursday, September 3, 2009 - 7:02:20 AM - peteo Back To Top (3990)

Ok here is the problem I have had with groups. You cannot assign a default schema to the group. Therefore if you have, say, an admin group that is creating tables in a particular database you will all of a sudden have the named schema from each user assigned to the tables they create. This looks ugly real quick.


It is possible that there is an easy solution to this but I cannot find one. If you have any further insight because we would LOVE to use this for management!!


Thursday, September 3, 2009 - 6:39:17 AM - LeeFAR Back To Top (3989)

If you use Windows security to grant permissions to your databases, how do you then prevent users from accessing the database and its objects when the users use something other than the intended application?  For example, let's say you have a custom application and that has a SQL backend.  And you have an AD group that you put users into and the permission that AD group to the database and the objects (stored procedures).  Now when the users run the custom application, the app connects using the Windows identity of the user.  But now let's say that user goes into something like Excel and connects to your database.  How do you prevent that person from executing the stored procedures?  For more thoughts, look at this MSDN post.


get free sql tips
agree to terms