SQL Server Windows Authentication with Users and Groups

By:   |   Comments (3)   |   Related: > Security


One of the best ways to keep SQL Server secure is to use Windows Authentication. This requires the integration of Active Directory (AD) security objects like Users and Groups into SQL Server. The purpose of this tip is to provide a scalable and manageable approach that takes into consideration the ever-increasing number of instances and databases that you need to manage in your environment and how this security approach can make managing SQL Server security much easier.


For the purpose of this tip, I will use the following SQL Server instance names: INS001 and INS002.

We will use the AdventureWorks sample database to apply our security settings.  Below you can see that this has been restored as AdvWorks.

object explorer

Just a word of warning, I will be using a domain controller later on in the tip.

Configure Active Directory Groups and Users

In my local Active Directory "MyDomain.local", I have setup a folder called SqlServerUsersAndGroups with the following Users and Groups:

directory users

These groups and users should be sufficient for our demonstration.

Users now have to be assigned to their corresponding Active Directory groups. Right click on MsSqlUser001 and select "Add to a group…" as shown below:

active directory groups

Choose SqlReaderGroup for MsSqlUser001 as shown below:

select groups

For MsSqlUser002 add to SqlDeveloperGroup and for MsSqlUser003 add to SqlDBAGroup.

Step 1: Administrating access at the SQL Server Instance Level

SQL Server security has many layers and one of them is at the instance level. For every AD object (user or group) that needs access to the SQL Server instance a login is required within SQL Server. For the scalability of SQL Server access, I strongly recommend only adding Active Directory groups and avoid user logins. Using just groups provides a very robust approach when managing access to hundreds of users. Furthermore, one user can have multiple group memberships in AD.

Creating SQL Server Logins

The following SQL command adds the three AD groups mentioned earlier to the SQL Server instance:

USE [master]

Once the command is run, your Logins folder in SSMS should look like this where we can see the three new logins:

sql dba group

Remember to do this for all of the SQL Server instances (INS001, INS002).

Create SQL Server Roles and Add Members

For the SqlDBAGroup, we will can use Server Roles to give permission at the server level. You have the option to either use the Fixed Server-Level Roles or create your own custom Server-Level Role. One benefit is that with the custom role, you can design very specific privileges precisely for your system. You can learn more about User Defined Server Roles here.

Let's create a User Defined Server-Level Role that has the ability to create and drop databases as well as administer logins. Additionally, it needs the ability to connect to server and view definitions.  The following code will grant these permissions.

USE [master]
CREATE SERVER ROLE [udsr_dba] – User Defined Server Role DBA
GRANT ALTER ANY LOGIN       TO [udsr_dba]
GRANT CONNECT SQL           TO [udsr_dba]

Add SqlDBAGroup to the new user defined server role udsr_dba as follows:


Step 2: Database Level Security

One level below SQL Server instance security is database level security. The difference between the two is that instance security defines rights and permissions for the whole SQL Server instance, whereas database security is specific to an individual database and can provide specific permissions to each database object, such as a table, stored procedure, view, etc.

The SQL Server database security has two types of objects "Users" and "Roles". By default, these look as follows in the AdvWorks database:

database security

Create Database Users

The way Database Security works is that a SQL Server login maps to database user in a database. Database users in turn can have role membership in none, many, or several database roles. Database users also can own none, one or many schemas in the database, but this is not a direct security feature. Owning a schema also grants ownership to schema objects and suppresses permission checking.

Let's illustrate database security in practice by adding our new logins to database AdvWorks and assign them their corresponding roles:

USE [AdvWorks]

CREATE USER [SqlDeveloper] FOR LOGIN [MYDOMAIN\SqlDeveloperGroup]
CREATE USER [SqlReader]    FOR LOGIN [MYDOMAIN\SqlReaderGroup]

Once created, the list of database users will look like this:


Create Database Roles

Our AdvWorks database has corresponding users for every new SQL Server login but the only permission in the AdvWorks database these users have now is the default "Public" role.

In order to grant data access, let's create database roles with custom access levels and assign membership to those roles:

USE [AdvWorks]
CREATE ROLE [db_sql_reader]
CREATE ROLE [db_sql_developer]
CREATE ROLE [db_sql_dba]

Assign Permissions to Roles

To make this case realistic and to differentiate the db_sql_reader role from db_datareader, I would like this role to have read access to all objects in SalesLT schema and avoid the dbo schema as it contains system information that is more relevant to a DBA. In addition, it would be beneficial to grant permissions to execute stored procedures.

This can be done by allowing a general grant execute and grant select to the SalesLT schema for the db_sql_reader role:

USE [AdvWorks]
GRANT EXECUTE ON SCHEMA::[SalesLT] TO [db_sql_reader]
GRANT SELECT  ON SCHEMA::[SalesLT] TO [db_sql_reader]

The db_sql_developer developer database role needs full access to the database, but assigning developers the db_owner database role will give more permissions than developers need, so we will limit permissions with the role.

The permission developers typically need are the following: select, delete, update, insert and execute on the SalesLT schema:

USE [AdvWorks]
GRANT EXECUTE ON SCHEMA::[SalesLT] TO [db_sql_developer]
GRANT SELECT  ON SCHEMA::[SalesLT] TO [db_sql_developer]
GRANT INSERT  ON SCHEMA::[SalesLT] TO [db_sql_developer]
GRANT UPDATE  ON SCHEMA::[SalesLT] TO [db_sql_developer]
GRANT DELETE  ON SCHEMA::[SalesLT] TO [db_sql_developer]

The db_sql_dba database role requires access to error logs and other system data located in dbo schema and be done as follows:

USE [AdvWorks]
GRANT EXECUTE ON SCHEMA::[dbo] TO [db_sql_dba]
GRANT SELECT  ON SCHEMA::[dbo] TO [db_sql_dba]
GRANT INSERT  ON SCHEMA::[dbo] TO [db_sql_dba]
GRANT UPDATE  ON SCHEMA::[dbo] TO [db_sql_dba]
GRANT DELETE  ON SCHEMA::[dbo] TO [db_sql_dba]

Once the code has been executed, the database roles folder should look as follows:

database roles

Assign Roles to Users

It is time now to assign database users to respective database roles. I would recommend assigning only one corresponding database role to a database user to keep a one-to-one relationship. Keep one-to-many relationships between users and groups in Active Directory.

USE [AdvWorks]
ALTER ROLE [db_sql_dba]       ADD MEMBER [SqlDBA]
ALTER ROLE [db_sql_developer] ADD MEMBER [SqlDeveloper]
ALTER ROLE [db_sql_dba]       ADD MEMBER [SqlDBA]

You can check the database role membership by right clicking on a database user and selecting properties.  For example, we can see this for user SqlDeveloper below that has access to the role db_sql_developer.

database user

This completes the setup of database and server security.

Verification of SQL Server Security Settings

Table access verification can be performed with the help of system store procedure sp_table_privileges. This store procedure returns the list of all table permissions. You can find more details on the sp_table_privileges store procedure here.

The below T-SQL script returns table privileges for all tables in the database:

CREATE TABLE #TempTablePrivileges
  TABLE_QUALIFIER nvarchar(255),
  TABLE_OWNER nvarchar(255),
  TABLE_NAME nvarchar(255),
  GRANTOR nvarchar(255),
  GRANTEE nvarchar(255),
  PRIVILEGE nvarchar(255),
  IS_GRANTABLE nvarchar(255)
USE AdvWorks
INSERT INTO #TempTablePrivileges
EXEC sp_table_privileges @table_name = '%%'
SELECT * FROM #TempTablePrivileges
DROP TABLE #TempTablePrivileges 

The script returns all table privileges including system ones like dbo and INFORMATION_SCHEMA and sys. If you are only looking for user defined table permissions, exclude these results from the query above. This tip has not granted any specific table privileges; hence this query should not return any user defined table permissions.

Schema privileges can be checked using sys.database_permissions in with the query below:

USE AdvWorks
   obj.name AS schema_name, 
   prin.name AS principal_name, 
   type_desc AS principal_type  
FROM sys.database_permissions as db_perm
JOIN sys.schemas as obj ON db_perm.major_id = obj.schema_id
JOIN sys.database_principals prin ON prin.principal_id = db_perm.grantee_principal_id
WHERE class = 3 -- SCHEMA ONLY 

We can check actual access rights of each individual server login by switching to a specific login and then trying to execute a statement. To do this, we use the EXECUTE AS statement.

See the code example below:

USE AdvWorks
SELECT * FROM [dbo].[BuildVersion]

More detailed information on EXECUTE AS can be found here. In our case, the select permission will be denied to MsSqlUser001 as it is only a member of SqlReaderGroup in Active Directory and this group is only given read and execute permissions to the SalesLT schema in our database. Therefore, select permissions is denied on dbo schema as shown below:

error message

Migrating database from one SQL Server instance to another

Now that security architecture is done for SQL Server INS001, we can demonstrate a migration of the AdvWorks database to instance INS002. This instance, in our case is installed on the same virtual machine node. Such migrations typically performed in large database development projects where the same database is copied between Development, Test and Production SQL Server instances. Before you proceed, please check that the security set-up of your server instances is identical as shown in step 1.

The simplest method to migrate a database is using backup and restore. If you do not know how to back up your database from one instance to another, you can find more help in this tip.

Restore this database to INS002.

Here is a sample database restore script:

USE [master]
FROM DISK = N'C:\MSSQL\Backup\AdvWorks.bak' 
MOVE N'AdventureWorksLT2012_Data' TO N'C:\MSSQL\DATA\AdventureWorksLT2012.mdf',
MOVE N'AdventureWorksLT2012_Log' TO N'C:\MSSQL\DATA\AdventureWorksLT2012_log.ldf',

Change the folder path to suit the environment you have created. More help on database restores can be found in this tip.

If the SQL Server logins have been created before we restore the AdvWorks database in INS002, the database users will be correctly mapped to their corresponding SQL server logins. If not, you will need to manually assign users to logins as follows which can be done with this script.

USE [AdvWorks]
ALTER USER [SqlReader]    WITH LOGIN=[MYDOMAIN\SqlReaderGroup]
ALTER USER [SqlDeveloper] WITH LOGIN=[MYDOMAIN\SqlDeveloperGroup]
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Semjon Terehhov Semjon Terehhov is an MCSE in Data Management and Analytics, Data consultant & Partner at Cloudberries Norway.

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

Wednesday, March 2, 2022 - 11:01:14 PM - Mz Back To Top (89849)
Donít you think it will be a security risk if individual users are managed outside of SQL Server via AD groups? Unless DBA has access to AD groups, then heíll never know who is in the system. Do you expect DBAs to then manage AD groups?

Instead how about adding AD accounts in SQL Server and then add them to roles in SQL Server.

Tuesday, October 5, 2021 - 4:13:42 AM - Semjon Terehhov Back To Top (89306)
Hi Scott. Yes, this is a copy paste mistake from my side. Glad that you have found this article useful :)

Thursday, September 30, 2021 - 10:03:47 AM - Scott H Back To Top (89294)
Thank you so much for this article. Just what I needed to deal with a challenging SQL permissions project. I noticed in the "Assign Roles to Users" section you have the line for "ALTER ROLE [db_sql_dba]....." listed twice. I assume you meant to have a line for the [dbl_sql_reader] there?

get free sql tips
agree to terms